incorporating a formula as a sheet name-CodePudding

How to create VBA macro that'll incorporate

=TEXT(TODAY()-DAY(TODAY()),"MM")

and

=TEXT(TODAY()-DAY(TODAY()),"YY")

for my tab names

I currently complete a report for work every month, and every month the tab names change. Example: "Keeps MM-YY", "Keeps and Drops MM-YY" or "Keeps 11-22", "Keeps and Drops 11-22".

I want to make a macro that looks for a tab named based off dates "MM-YY" of the previous month.

I tried the following, but had no luck

Sub KeepsTab

Dim Month as String
Dim Year as String

Month = TEXT(TODAY()-DAY(TODAY()),"MM")
Year = TEXT(TODAY()-DAY(TODAY()),"YY")

Sheets("Keeps Month-Year").Select

End Sub

CodePudding user response：

This is failing as it is explicitly looking for a sheet called "Keeps Month-Year". You could do a quick fix as

Sheets("Keeps "& Month & "-" & Year).Select

Are you using the functions "WorksheetFunction.Text", "WorksheetFunction.Day" and so on? I believe this are not built-in in VBA, so you may want to check that out.

Last but not least, may I suggest dim-ing a new variable strSheetName so that you concat the name beforehand and then do the lookup?

strMonth = WorksheetFunction.Text(WorksheetFunction.Today()-WorksheetFunction.Day(WorksheetFunction.Today()),"MM")
strYear = WorksheetFunction.Text(WorksheetFunction.Today()-WorksheetFunction.Day(WorksheetFunction.Today()),"YY")
strSheetName = "Keeps " & strMonth & "-" & strYear
Thisworkbook.Worksheets(strSheetName).Select

Filling in the gaps, of course. This should do it.