Forum Discussion
Excel Macro Reference to Current Workbook/Worksheet, Not a Specific One
I do a CSV download of banking data from my online account. Those data must be reconfigured (deleting, adding, and combining columns; turning positive numbers into negatives; sorting; etc) to be pasted into an Excel workbook in which I track my spending.
I developed a 59-line macro to do all the reconfiguring for me and it runs perfectly if the CSV file (that I "Open with..." Excel) always has the same name...AccountHistory. But if I don't remember to delete that file from my Download folder, any subsequent downloaded CSV file gets a different name...AccountHistory-2, AccountHistory-3, etc.
The problem is that certain lines of the macro (where I am sorting the rows) refer specifically to AccountHistory, so if I want to run the macro on AccountHistory-2, the macro locks up when it gets to there. Here is the code:
ActiveWorkbook.Worksheets("AccountHistory").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("AccountHistory").Sort.SortFields.Add2 Key:=Range( _
"C2:C1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("AccountHistory").Sort
.SetRange Range("A1:E1000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
How can I get the macro to reference the workbook/worksheet on which I am running the macro, regardless of its workbook (and worksheet) name?
PS - AccountHistory is only referenced in the sorting section of the macro.
Replace ALL occurrences of ActiveWorkbook.Worksheets("AccountHistory") with ActiveSheet
2 Replies
Replace ALL occurrences of ActiveWorkbook.Worksheets("AccountHistory") with ActiveSheet
- scrail2004Brass Contributor
Thanks HansVogelaar! Worked like a charm. I've got another macro question coming up. Keep your eyes peeled for it. :-)