Rename a worksheet with a new name

[DLRosen46]

I created a macro to format a daily report. The file, and hence, the worksheet will always be named "ACK_#", where # is the day of the month (ex. ACK_23). I want to create a copy of the worksheet named, "ACK_#" and rename it "Summary." My problem is that I created the macro from a file/worksheet named "ACK_23" but I need the macro to work for any day.

Here is the code at issue:

Sub ACK_for_ADV()

' ACK_for_ADV Macro
...
Sheets("ACK_23").Select
Sheets("ACK_23").Copy Before:=Sheets(1)
Sheets("ACK_23 (2)").Select
Sheets("ACK_23 (2)").Name = "Summary"

I basically want to replace "ACK_23" with a wildcard or variable, but I'm not sure how to do this.

 

[cerfani]

Dim s As String

s = ...
Sheets("ACK_" & s)

you just got to get the right number wherever it is

 

[Norie]

Perhaps.

Sub ACK_for_ADV()

' ACK_for_ADV Macro
...
Sheets("ACK_" & Day(Date())).Copy Before:=Sheets(1)
Sheets(1).Name = "Summary"