200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I'm trying to get some tabs in my workbook to change depending on what is in a cell reference. The cells have formulas and sometimes show a blank cell in which case I would like the sheet to hide or just show mon?
I would like the following please.
Tab = Cell
Mon1 = Cell E28
Mon2 = Cell J28
Mon3 = Cell L28
Mon4 = Cell N28
Mon5 = Cell P28
Mon6 = Cell R28
Mon7 = Cell E30
Mon8 = Cell J30
Mon9 = Cell L30
Mon10 = Cell N30
Mon11 = Cell P30
Mon12 = Cell R30
Albert
Don't have much time but the code below (when placed behind active worksheet "Cover") will rename the tabs to suit.
Private Sub TabNames()
Dim ws As Worksheet, Arr() As Variant
Arr = [{"$E$28","$J$28","$L$28","$N$28","$P$28","$R$28","$E$30","$J$30","$L$30","$N$30","$P$30","$R$30"}]
ThisWorkbook.Unprotect Password:="242200"
For n = LBound(Arr) To UBound(Arr)
With Sheets(n + 3)
.Unprotect Password:="242200"
.Name = Format(Range(Arr(n)).Value, "mmm yy")
.Unprotect Password:="242200"
End With
Next n
ThisWorkbook.Unprotect Password:="242200"
End SubYou just have to add a line test for nothing in the cell (and to hide that sheet)
Hope this helps.