Forum Discussion

scrail2004's avatar
scrail2004
Brass Contributor
Aug 21, 2025
Solved

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

    • scrail2004's avatar
      scrail2004
      Brass Contributor

      Thanks HansVogelaar!  Worked like a charm.  I've got another macro question coming up. Keep your eyes peeled for it.  :-)

Resources