Macro needs to ignore unique tab name

[YLand]

I've created a Macro to format my monthly reports every month. The problem I am having is that my report automatically downloads every time with a different Tab Name. Today the tab is named 2023-10-24@01_22_report and tomorrow it will automatically be 2023-10-25@01_22_report. My macro does not recognize the report because of the different Tab name. What do I need to change on my macro? I've tried to google and follow other responses, but can't figure out where exactly I need to make the changes, I am not familiar with VBA and codes. Here is what my code looks like, please tell me what I need to change or add.

 

[kevin9999]

my report automatically downloads every time with a different Tab Name

Does the report you download every time only have one Tab? If so, you could refer to it by index number rather than actual name. So instead of:

ActiveWorkbook.Worksheets("2023-10-24@01_22_report").etc

you could use:

ActiveWorkbook.Worksheets(1).etc

Although you would be better off assigning a variable to that name, like this:

Dim ws as Worksheet
Set ws  = ActiveWorkbook.Worksheets(1)

Then for the rest of the code you simply refer to ws

 

[YLand]

The original report only has 1 tab (see pic), my macro creates 3 different tabs based on that data. Could I still use a variable? If so, where would I change it? Sorry, again, I am new to this and you might need to show me.

 

[kevin9999]

If you look in the project window (upper left in your image) you can see that the sheet has the code name of Sheet1.
Sheet1("2023-10-24@01_22_report")

If the sheet of interest is always Sheet1 in your project, then because the sheet is in the file that contains the VBA code, you can safely refer to the sheet by its code name rather than its Tab name. So in your code you can replace every line that looks like this:

ActiveWorkbook.Worksheets("2023-10-24@01_22_report").<something>

with this:

Sheet1.<something>

for example

ActiveWorkbook.Worksheets("2023-10-24@01_22_report").Sort.SortFields.Clear

becomes

Sheet1.Sort.SortFields.Clear

and that way, you don't have to change your code with every new report that's run.

 

[YLand]

Awesome, thank you for the explanation. It works until I get to this section. What should I be changing this to? I changed it to Sheet1.

 

[kevin9999]

Most "Select" lines are unnecessary. Delete that line - and the one before it (Range("B1").Select) which adds nothing to your code functionality.

 

[YLand]

You have been very helpful, I did that and now some of the formatting is off. I will continue to play with it following your instructions, thank you.

 

[kevin9999]

When it comes to formatting, you need to be careful. You can still get rid of some of the Select lines, for example this:

Columns("C:D").Select
Selection.NumberFormat = "0.00"

Could be simply written like this:

Columns("C:D").NumberFormat = "0.00"