Excel VB This Active Worksheet

excel worksheet vba
Dan Twining picture Dan Twining · Oct 21, 2012 · Viewed 47.2k times · Source

I'm sure this is really easy but I can't find any info on it.

I've got a Excel macro which has things like:

    ActiveWorkbook.Worksheets("Data 21 Oct 12 11-05").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data21 Oct 12 11-05").Sort.SortFields.Add Key _
    :=Range("W1:W23"), SortOn:=xlSortOnValues, Order:=xlDescending, _
    DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data 21 Oct 12 11-05").Sort
    .SetRange Range("A1:BZ23")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

What I want to do is make this a global macro but the file name is hard coded in the macro. How can I change the code so it references "this" currently open file?

Answer

Dan Twining picture Dan Twining · Oct 21, 2012

Ok, I worked out you need to change it from:

ActiveWorkbook.Worksheets("Data 21 Oct 12 11-05").Sort.

to:

ActiveWorkbook.ActiveSheet.Sort

Credit to this SO item on creating a button in Excel

Related questions

How to add a named sheet at the end of all Excel sheets?

I am trying to add an Excel sheet named "Temp" at the end of all existing sheets, but this code is not working: Private Sub CreateSheet() Dim ws As Worksheet ws.Name = "Tempo" Set ws = Sheets.Add(After:=Sheets(Sheets.…

Read More
Excel VBA For Each Worksheet Loop

I am working on code to basically go through each sheet in my Workbook, and then update column widths. Below is the code I wrote; I don't receive any errors, but it also doesn't actually do anything. Any help is …

Read More
Is there a macro to conditionally copy rows to another worksheet?

Is there a macro or a way to conditionally copy rows from one worksheet to another in Excel 2003? I'm pulling a list of data from SharePoint via a web query into a blank worksheet in Excel, and then I want …

Read More