Menu
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?
668
In spreadsheet programs such as Excel or Google Spreadsheets, the active cell is identified by a colored border or outline surrounding the cell. The active cell is always in the active sheet.
To activate a workbook using VBA, you need to use the Workbook. Activate method. In this method, you need to specify the workbook name using the Workbook object. It also allows you to use the workbook number instead of the workbook name, but you can only refer to the open workbooks.
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
139
ActiveWorkbook does reference the current open and active file.
If by file name you refer to the fact that the sheet name is hard coded, you can make the macro work with the active sheet by replacing ActiveWorkbook.Worksheets("Data 21 Oct 12 11-05") with ActiveSheet.
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
