VBA Tab Names

[RMM4487]

Hey guys,

I'm fairly new to VBA but progressing daily. I usually can dig up solutions to issues with a quick search on here but I haven't seem to come up with a solution to my current problem. Here's the deal:

I currently am building an automated financial model; the model creates a new tab and pulls in data from another book; nothing revolutionary here. However, the problem I'm having is using VBA to rename the tab. The reason is because I need the name of sheet to be a specific value and format. The tab needs to be renamed "MIAC" + today's date, formatted as "D.MM" with the separator literally being "." . For example today's tab would be named "MIAC 7.26". I set up a formula in Excel to display the date formatted with MIAC in front however, I have tried having VBA directly copy the cell with custom formatting to display the date as MIAC + Date, I've tried setting a variable as that cell and setting the sheet name to that variable, and directly setting the sheet name to that cell however in all instances I lose the format and my tab name ends up being just the date code (i.e. 49233 etc.)

Any work around for this?

Thanks !

Ryan

 

[XLfriend777]

Hi RMM4487 - If I understand your quest correctly you might try the code below. Hope this helps.

Sub RMM4487_tab_name()
Dim BuildTheTabName As String
Month1 = Month(Now())
Day1 = Day(Now())

BuildTheTabName = "MIAC" & " " & Month1 & "." & Day1

    ActiveSheet.Name = BuildTheTabName
End Sub

 

[Fluff]

Hia
how about

    ActiveSheet.Name = "MIAC " & Day(Date) & "." & Month(Date)

 

[My Aswer Is This]

Try this:

ActiveSheet.Name = "MIAC " & Format(Date, "M"".""d")

 

[RMM4487]

That worked perfectly! Why didn't I think of that

Thanks!

 

[RMM4487]

Thanks all, looks like all 3 of those work. Appreciate the quick help!

 

[Fluff]

Glad we could help
Thanks for the feedback

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thanks all, looks like all 3 of those work. Appreciate the quick help!

 

[XLfriend777]

Glad to help. I really enjoyed the efficient code in the other answers. All the best!