Free Microsoft Excel 2013 Quick Reference

Active Worksheet

    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Rn, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data").Sort
this is a snippet of the code I worked on (via macro recorder) to sort data. If I convert this macro to an add-in, the worksheet won't always be named "Data". So, I want to modify this code so that the macro acts on the Active worksheet. any suggestions?

Post your answer or comment

comments powered by Disqus
Can anyone tell me how i could send an active worksheet from excel as an e-mail attachment (Microsoft Outlook) instead of the body of the e-mail? I have a workbook with about 30+ tabs and I only want to send the active tab (worksheet) to the e-mail recipient.

Thanks in advance,


I have problem to activate worksheet with VBA Code, I have macro that
runs OK until I open VBA Editor and run that macro few times.

I have about 20 worksheets and have to switch between to get data from Oracle, wenn I switch manual and run my macro that it works but wenn i'm on a different worksheet as destination that no go.
with .Activate does not work.

Wenn I close Excel and open then all works fine.

Anyone know this problem?

Hi All

I have a loop that selects worksheets that meet criteria in one workbook. When one of the worksheets is activated (i.e. it meets the criteria) I want to get that worksheet, put it into a temp workbook, then continue through the loop to check if other sheets meet the criteria. Once all worksheets are checked I send the temp workbook as an attachment. I am a little unclear on how to insert the active worksheet into a temp workbook...if anybody could give me a little nudge in the direction of the correct syntax for this it would be much appreciated.

basic code looks a little like this (where line starting with >>> is where I get a bit stuck)

do while not last activeSheet

if ActiveSheet name = strCheckName then

>>> get active sheet and add the active sheet to temp workbook

end if

move next worksheet



Hello ---

I have this code that saves my file to a specific location. For some reason the "Contro, paste special, values" portion does that function to the original workbook and then saves it to the specified location. The problem with that is that it gets rid of all cell references on the original file and the new one created cell references the original file.

What I need is for the function to create a new workbook with only the active worksheet on it. I need that workbook to be the "Control, paste special, values" portion of the original (so that it does not cell reference another file).

Here is the code, please help me out as I'm not sure what's going wrong.

Thanks in advance!

Sub SaveAsExcel() 
    Dim WS As Worksheet 
    Dim MyDay As String 
    Dim MyMonth As String 
    Dim MyYear As String 
    Dim MyPath As String 
    Dim MyFileName As String 
    Dim MyCellContent As Range 
    Application.ScreenUpdating = False 
    MyDay = Day(Date) 
    MyMonth = Month(Date) 
    MyYear = Year(Date) 
    MyPath = "[URL="file://Ho-0001-ncham/"]Ho-0001-ncham[/URL]" 
     ' use declared Windows API function to set the path
    SetCurrentDirectoryA (MyPath) 
    Set WS = ActiveSheet 
    Set MyCellContent = WS.Range("B3") 
    MyFileName = "Agent " & Range("A2").Text & " " & Format$(Date, "mm-dd-yyyy") 
    With WS.UsedRange 
        .PasteSpecial xlPasteValues 
    End With 
    Application.CutCopyMode = False 
    Application.WindowState = xlMinimized 
     ' ChDir MyPath
    If CInt(Application.Version)

Basically what I am trying to do is. I have a workbook with about 12 worksheets within it. I am trying to input a button on worksheet 3 that when pushed would activate worksheet 12. Basically the same as if you clicked on the tab at the bottom of the screen called worksheet 12.

At this point I am lost. Could someone help me. I realise that you have to create a button which Ive done. However I cannot seem to figure out the proper macro code to get the button to change the current worksheet when its pushed. Any ideas?

Hello, I am trying to use a very simple VBA macro to recalculate a range in a non-active worksheet without recalculating everything...I basically try the following:

Worksheets("Calc").Range ("BH38:CC47") 
Application.Run "OnKeyCalculateSelection" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which returns "object does not support this property or method"


Worksheets("Calc").Range ("BH38:CC47").Select 
Application.Run "OnKeyCalculateSelection" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which says "Select method of clas range failed"

if i try the two pieces of code above using the vba name of the sheet ("sheet1") instead of "calc" i have the error "Subscript out of range"

can anyone help me with that? I've been spending almost 2 days on it now...

I am wondering if someone could possibly help me as I am an amateur to Excel.
I can do what I require using incell formulas but would like to do it using VBA code.
I have Monthly Worksheets and wish to copy the complete range B6:U1493 to the active Worksheet when the Month I wish to use is selected from a Drop Down list of Worksheet Names.
All the cell ranges in all the Worksheets including the Active Worksheet are the same (B6:U1493)

Any help would be greatly appreciated



I'm looking for a macro to automatically rename the active worksheet to Sheet1.

Thanks for your guidance & help.

I have the following code which allows users to print the current worksheet as long as the current active worksheet is "Growth" or "G&I". It would be much better if I just designated in the code to print the specific worksheet instead of the active on. What code would I use to print the specific worksheet and not the active workseet?

Many thanks in advance


    If ActiveSheet.Name = "G&I" Or ActiveSheet.Name = "Growth" Then 
        Application.ScreenUpdating = False 
        ActiveSheet.PageSetup.PrintArea = "$a$1:$Q$170" 
        With ActiveSheet.PageSetup 
            .LeftHeader = "" 
            .CenterHeader = "" 
            .RightHeader = "" 
            .LeftFooter = "Information as of &D" 
            .CenterFooter = "Confidential" 
            .RightFooter = "Daniel K. Speirs, Financial Consultant" 
            .LeftMargin = Application.InchesToPoints(0.05) 
            .RightMargin = Application.InchesToPoints(0.05) 
            .TopMargin = Application.InchesToPoints(0.05) 
            .BottomMargin = Application.InchesToPoints(0.05) 
            .HeaderMargin = Application.InchesToPoints(0.5) 
            .FooterMargin = Application.InchesToPoints(0) 
            .PrintHeadings = False 
            .PrintGridlines = False 
            .PrintComments = xlPrintNoComments 
            .PrintQuality = 600 
            .CenterHorizontally = True 
            .CenterVertically = False 
            .Orientation = xlPortrait 
            .Draft = False 
            .PaperSize = xlPaperLetter 
            .FirstPageNumber = xlAutomatic 
            .Order = xlDownThenOver 
            .BlackAndWhite = False 
            .Zoom = False 
            .FitToPagesWide = 1 
            .FitToPagesTall = False 
            .PrintErrors = xlPrintErrorsDisplayed 
            Application.ScreenUpdating = True 
        End With 
        Application.ActivePrinter = "RSTB1TBCOLOR1 on Ne02:" 
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ 
        "RSTB1TBCOLOR1 on Ne02:", Collate:=True 
        Application.ScreenUpdating = True 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi all,
I am quite new to VBA and I am trying to create a macro that does the following:

1. WorkSheet "Email" contains 3 columns: 1st: Full name; 2nd: email; 3rd: choice (yes or no)

2. There are other worksheets and on each worksheet, there is a "Send email" button. Whenever people press this button, it will send an email to people on the "Email" with choice = 'yes'. And this active worksheet will then become the body (in HTML format) of the email.

I got the following code from this forum:

     ' This example use late binding, you don't have to set a reference
     ' You must be online when you run the sub
    Dim iMsg As Object 
    Dim iConf As Object 
    Dim cell As Range 
     '    Dim Flds As Variant
    Dim lname As String 
    Dim Msg As String 
    Dim Response 
    Dim Answer 
    Dim Question As String 
    Application.ScreenUpdating = False 
    Set iConf = CreateObject("CDO.Configuration") 
    iConf.Load -1 ' CDO Source Defaults
    Set Flds = iConf.Fields 
    With Flds 
        .Item("") = 2 
        .Item("") = "LEED" 
        .Item("") = 25 
    End With 
    lname = 
    Msg = "Are you sure you want to send an email?" 
    Response = MsgBox(Msg, vbYesNo) 
    If Response = vbYes Then 
        Question = "***** Please specify what you have changed *****" 
        Answer = InputBox(Question) 
        For Each cell In Sheets("EmailList").Columns("B").Cells.SpecialCells(xlCellTypeConstants) 
            If cell.Value Like "*@*" And LCase(cell.Offset(0, 1).Value) = "yes" Then 
                Set iMsg = CreateObject("CDO.Message") 
                With iMsg 
                    Set .Configuration = iConf 
                    .To = cell.Value 
                    .From = Environ("USERNAME") & "" 
                    .Subject = "File Updated" 
                    .HTMLBody = "The """ & lname & """ Worksheet has been updated." _ 
                    & "
" _ & "
" _ & "Changes: " & Answer _ & "
" _ & "
" _ & "Library Link" _ & "
" _ & "
" _ & SheetToHTML(ActiveSheet) .Send End With Set iMsg = Nothing End If Next cell Set iConf = Nothing Else Exit Sub End If Application.ScreenUpdating = True End Sub Public Function SheetToHTML(sh As Worksheet) 'Function from Dick Kusleika his site ' 'Changed by Ron de Bruin 04-Nov-2003 Dim TempFile As String Dim Nwb As Workbook Dim myshape As Shape Dim fso As Object Dim ts As Object sh.Copy Set Nwb = ActiveWorkbook For Each myshape In Nwb.Sheets(1).Shapes myshape.Delete Next TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Nwb.SaveAs TempFile, xlHtml Nwb.Close False Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) SheetToHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Set Nwb = Nothing Kill TempFile End Function
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

However, sometimes, it doesn't work especially after people using Office 2003 save the file, it will be corrupted. When the next person opens the file and try to send an email, it complains the Fld and then LCase, and even complaint the "ENVIRON". The error is "Compile Error - Can't find project or library"

And the weird thing is that even if I just modified one line, I got the same error.

Any ideas?


I want a maco to delete specific shapes (all of the msoShapeOval's) on the active worksheet. I have other objects on the worksheet so I can't use the following code:

Delete all shapes
Use this macro to delete all shapes on the worksheet
Sub DeleteAllShapes1()
On Error Resume Next
ActiveSheet.DrawingObjects.Visible = True
On Error GoTo 0
End Sub I've tried this code and it doesn't work:

Delete Ovals
Sub ShapesDeleteOvals()
'Delete all Objects except Comments
On Error Resume Next
ActiveSheet.DrawingShapes.Shapes(msoShapeOval).Visible = True
On Error GoTo 0
End Sub Any ideas???


Hi all,

I'm thinking this is very simple one but I can't figure it out myself, I'm just learning at the moment. I already have the below code running no problem at all and it does half the job great. It takes each worksheet name and creates a new workbook with the same name.

Sub SplitSheets()
Dim W As Worksheet
For Each W In Worksheets
W.SaveAs ActiveWorkbook.Path & "/" & W.Name
Next W
End Sub

However, it needs to delete all the non active worksheets from each new workbook so that each workbook only contains one worksheet of the same name. I'm thinking this can just be innclkuded in the above after the save command but can't get it to work correctly. I've had a search through the forum and can't see anything, it's probably so simple nobody has needed to ask!



Set Cell on Active Worksheet Based On Filter

I am filtering by date and then by name and would like the name that i filter by to show up on cell B2.

I am unsure how to accomplish this task. any help would be appriciated.

Thank You!

I need VBA code that will clear values only including values that have been added and subtracted for eg = 2500+275+285-80

All formula's for eg =sum, = b10+c10 etc and text must be left.

I only want the values in the active worksheet cleared for eg Aug 2007

Your assistance will be most appreciated


If I am selcting cells on a non-active worksheet, I now do something like this (below) as the qwuickest way to select the cell:

Without asking why I am "Selecting" vs. Activating the cells (there are several reasons for this particular thing I am doing), I'm just wondering is there a single line of code (versus my 2 line method of code written above) which will SELECT (not Activate) the given cell on a NON ACTIVE worksheet? In other words, this will NOT work if Sheet2 is NOT the active sheet:

'This will NOT work if Sheet2 is NOT the active sheet
I'm hoping there is something like this syntax (above) that will work to activate and select a cell on a non-active sheet???

Thank You,

I have a workbook with numeral worksheets. I would like to have a macro that will allows me to do the following.
Copy the active worksheet to a new workbook with the same name as the worksheet. The workbook must have only one worksheet and the new workbook and worksheet is to have the same name.
If I highlight more than one worksheet in the active workbook, the macro must also be able to create multiple workbooks each with only one worksheet of the same name.


I want to create a macro that will allow me to use a shortcut that will:

(1) copy the values & the format (i.e. not the formulas) of the active
worksheet to a new workbook; and

(2) save that new workbook as "Claim" in a specified file location.

Hope someone can help

I want to create a macro which sends JUST the active worksheet to a
pre-defined e-mail address (e.g. ") but instead of
just immediately sending it as the Mail_Worksheet sub want to do, I want it
to open up the Outlook dialogue box with the active worksheet inserted as an
attachment so that the sender can add a message prior to clicking 'send'.

Just to make it more awkward, the active worksheet contains several
'VLookups' which reference sheets (2) and (3) which will not be emailed but
the eventual recipient of the email must nevertheless see the values. I'm a
beginner and this is too hard for me I'm afraid. Any ideas for a code ?

I have a wookbook with two sheets, named "Dept20" and sheet2. On sheet2 I
have my raw data; Cell A1 "Dept20", Cell B1 4,500.
On sheet Dept20 I want to write a vlookup that can reference the data on
sheet2 by relating to the name of the active worksheet rather than typing
"Dept20" in the vlookup formula.

Any ideas?



Something like

With Worksheets(Listbox1.Value)
.Visible = True
End With

but you can't hide them all, at least one must be visible


Bob Phillips

"NightProbe" > wrote in message
> Is it possible to have a worksheet (from a multiple worksheet
> workbook) popup as the active worksheet by selecting its name from
> within a listbox or combo box? I want to hide all the worksheets and
> provide a way that a particular worksheet is shown and then hid again
> after use. I can't seem to find any information on how to do this if
> at all possible. Any help will surely be appreciated!

Hello, I have recorded a macro and I need to know what command to give it to
advance the active cell down the spreadsheet to the first empty cell. Also,
how do I tell the macro to look at the active worksheet or file to run, not a
specific one. I want to run this macro on a lot of files. Right now the
macro has one specific file name and I need to be able to run the macro on
any file.


I have a workbook where each worksheet represents a different financial account. Each worksheet is a copy of a template worksheet I created. The worksheets call VBA functions that perform various financial calculations. The VBA functions are all located in one module.

My problem is that sometimes when I activate a worksheet, some of the data is from a different worksheet.

For example, in a particular cell I call function passing it a cell reference: “=yr2DtRtn($G11)”.

Sometimes this works correctly by returning a result based on the value in G11 of the active worksheet; and other times it seems to display values from other worksheets.

The assumption I’m making is VBA knows what the active worksheet is and uses it as a default. Am I wrong about this?


I'm using the following code (with the help of this newsgroup) to create and
update a list on a separate worrksheet. The users input the data into cells
A8:A501 of the Active worksheet, and the list is recapped (created) with no
duplicates or spaces on another worksheet (called "Adjustments") in cells

Here is the code used in the Active worksheet's module:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="test"
If Not Application.Intersect(Target, _
Range("A8:A501")) Is Nothing Then
gCopyUnique Range("A8:A501"),
End If

ActiveSheet.Unprotect Password:="test"
Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Protect Password:="test", DrawingObjects:=True,
Contents:=True, Scenarios:=True

End Sub

Here is the other part of the code that is located in the workbook's
standard module:

Public Sub gCopyUnique(rrngSource As Range, rrngDest As Range)
ActiveSheet.Unprotect Password:="test"
rrngSource.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=rrngDest, Unique:=True
ActiveSheet.Protect Password:="test", DrawingObjects:=True, Contents:=True,

End Sub

Here is my question... This code is working great...but I need to know if
there is a way to alter the above to code to make it reference the worksheet
"before" the Active Worksheet .... instead of the "Adjustments" worksheets
as shown above.

The name of the "Adjustments" worksheet will change as the users add more
sheets to the workbook (via code in an addin file)
I can continue to use the Active worksheet part of the code above...but the
part where it references the "Adjustments" worksheet..will not work.. if the
worksheet has the name "Adjustments (2)"...and so on.. will always
be the worksheet before the one were the users enter the data into..which is
the active worskheet.

Any help is greatly appreciated...
Thanks in advance!

I have VBA code to save an active worksheet as HTML, but I am lost when I try to email the HTML page from Excel.

I need to automatically send the HTML email to a customer using a confirmation number on the workseheet as well as the email addressee.

When I try to email an active worksheet to a customer too much detail is sent & some customers will not be able to read the excel page.

The following code represents only the saving of the HTML page:

Private Sub save_web_page_Click()
Dim Thisfile As String
On Error GoTo quit


Thisfile = Worksheets("confirmation_letter").Range("Reservation_Name").Value

ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="C:Documents and SettingsGregMy DocumentsReservationsReservation_web_pages" & Thisfile, _
Sheet:="confirmation_letter", Source:="Print_Area", HtmlType:=xlHtmlStatic).Publish

GoTo Endall
MsgBox "...WARNING... " & Thisfile & " HTML web page File not Saved"
End Sub

Any help will be greatly appreciated.


How do i create amacro to delete active worksheet. For example im using a macro to create a unique filter and paste the data to two new worksheets but i want the original worksheet to be deleted instead of me deleting it manually.

No luck finding an answer? You could always try Google.