dunamic tab name

[Scholar]

Hello People,

I find it frustrating to keep updating the tab name every month for a report I produce. Especially, since the month is made referrence to in a cell within the worksheet. Is there a way to maybe concatenate or create a formula to keep this current automatically?

Thanks for all your help...past and future!

 

[VoG]

You can do this with a macro like this

Sub Namesheet()
ActiveSheet.Name = Format(Range("A1").Value, "dd-mm-yyyy")
End Sub

change A1 to suit.

 

[p45cal]

or if your date is in say M4, then in an event (paste this into the sheet concerned's code module):

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Parent.Name = Format(Range("M4").Value, "MMM yy")
End Sub

 

[Scholar]

thanks you VoG. Can I apply my own format/wording in a cell that becomes the tab name like say, "Month of Mach 2010", rather then dd-mm-yyyy?

 

[Scholar]

ADVERTISEMENT

thank you p45cal. Now i am getting ahead of myself, as im a VBA newbie. What if i wanted to add a button to activate this macro?

 

[VoG]

Try

Sub Namesheet()
ActiveSheet.Name = "Month of " & Format(Range("A1").Value, "mmmm yyyy")
End Sub

You can assign this to a button from the Forms toolbar.

 

[Scholar]

perfect. thanks.