Selecting Tab with VBA that has a Dynamic Tab Name

[damrkstr]

Hi, I have looked for a solution to this and am unable to find one. I have a series of tab with a number code and month such as "12345 - April", and for subsequent months it will be "12345 - May" with the month changing each time for each of the number code tabs. I am referencing to the tabs directly in cells in my workbook using indirect formulas where I have constructed the tab name. How can I select and edit these tabs in VBA, so I can then make changes on that tab? I have tried the following but it is obviously not working. The date is in the "Report_Date range on the Inputs tab with the date from which the month that is in the tab name is pulled.
Thank you for your help!

Sub Tab_Selection()

Dim sheet_name As String

Sheets("Inputs").Select
sheet_name = "12345 - " & ActiveSheet.Range(Format("Report_Date", "MMMMM").Value

Sheets(sheet_name).Select

End Sub

 

[Joe4]

See if this works:

Sub Tab_Selection()

Dim rng As Range
Dim sheet_name As String

Set rng = ActiveWorkbook.Names("Report_Date").RefersToRange
sheet_name = "12345 - " & Format(rng.Value, "MMMMM")

Sheets(sheet_name).Select

End Sub

 

[damrkstr]

Thank you Joe4. I am getting an error on line Sheets(sheet_name).Activate. It is a Run-time error '9': Subscript out of range"
Thank you for your help!

 

[Joe4]

Can you add this line before that line, and confirm what it is returning?

MsgBox sheet_name

 

[damrkstr]

Thank you Joe. You are dead-on. It was returning "12345 - April4". Changing the format from Format(rng.Value, "MMMMM") to
Format(rng.Value, "MMMM") did the trick. Will that format be correct for other month names? Thank you!

sorry to latch on an additional question, but it is related because now that I have selected that dynamic tab and I was trying to write a formula in Cell AN19 on that page and copy it down the column but it seems like it is not working. Is there something wrong with this code that could be easily fixed or is it due to the tab reference?
Thank you!

Sub Tab_Selection()

Dim rng As Range
Dim sheet_name As String

Set rng = ActiveWorkbook.Names("Report_Date").RefersToRange
sheet_name = "12345 - " & Format(rng.Value, "MMMM")
MsgBox sheet_name
Sheets(sheet_name).Select

Range("AN19").Select
ActiveCell.Formula = "=A19&B19&TEXT(D19,"m/d/yyyy")&" - "&TEXT(E19,"m/d/yyyy")"

End Sub

 

[Joe4]

Changing the format from Format(rng.Value, "MMMMM") to
Format(rng.Value, "MMMM") did the trick. Will that format be correct for other month names? Thank you!

If you need the full month name, "MMMM" should work for all of them.
"MMMM" is full month name, no matter how long it is.
"MMM" is the three letter abbreviation for each month
"MM" is the two digit month number, with leading zero if necessary (i.e. "04")
"M" is the digit month number (1 or two digits, i.e. "4" and "11)

sorry to latch on an additional question, but it is related because now that I have selected that dynamic tab and I was trying to write a formula in Cell AN19 on that page and copy it down the column but it seems like it is not working. Is there something wrong with this code that could be easily fixed or is it due to the tab reference?
Thank you!

I am not sure I 100% follow what you are saying, but if you are trying to do the same thing in a native Excel formula that you are doing in VBA, note that the VBA function "FORMAT" and Excel function "TEXT" follow the same structure and rules.

So if you want full month name, you would use:
TEXT(D19,"MMMM")
instead of
TEXT(D19,"m/d/yyyy")

 

[damrkstr]

Thank you very much Joe. Right now in cell A19 on tab "12345 - April" I have the formula written "=A19&B19&TEXT(D19,"m/d/yyyy")&" - "&TEXT(E19,"m/d/yyyy")" and it is copied down the whole column from A19 to AN70. I would like VBA to basically write that formula for me in cell A19 and copy it down that column. My code above is currently not doing that for some reason even though I am now on the correct tab at least.
Thank you.

 

[Joe4]

Thank you very much Joe. Right now in cell A19 on tab "12345 - April" I have the formula written "=A19&B19&TEXT(D19,"m/d/yyyy")&" - "&TEXT(E19,"m/d/yyyy")" and it is copied down the whole column from A19 to AN70. I would like VBA to basically write that formula for me in cell A19 and copy it down that column. My code above is currently not doing that for some reason even though I am now on the correct tab at least.

The easiest way to get the Excel formula in VBA code is to turn on the Macro Recorder, and record yourself manually entering it in the first row you need it in.
If you stop and view your code, you will see a line like this:
ActiveCell.FormulaR1C1 = "=..."

So, if you want to apply that formula to the range AN19:AN70, then all you have to do, is copy that line over to your VBA code, and change ActiveCell to Range("AN19:AN70"), so you have:
Range("AN19:AN70") = "=..."

 

[damrkstr]

Thank you very much Joe! I just realize I didn't respond. That was very helpful!

 

[Joe4]

You are welcome.
Glad to have helped!