Tab Names to Match Cell Values when Changed
Assumptions: Summary sheet and the 4 "year" sheets are the first 5 sheets in
workbook (in case you have additional sheets) This is a worksheet event, so
right click on summary sheet tab and click "view code". Paste this in.
'===========
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
i = 1
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
For Each ws In Worksheets
If ws.Name < Me.Name Then
ws.Name = Me.Cells(i, "A")
i = i + 1
'In case you have other sheets past the 4 years
If i 4 then exit sub
End If
Next
End Sub
'=================
--
Best Regards,
Luke M
*Remember to click "yes" if this post helped you!*
"Johnny" wrote:
In cell A1 I have the year 2008.
In cells A2 through A4 I have a formula to add one year as I move down each
cell so that in A2 would be 2009, A3-2010 and so on. I also four worksheets
that are named for each of those years. I would like to have VBA code to
rename the TAB Names when I change cell A1 which will in turn change each for
the four TAB Names to match whats in cells A1 - A4 respectively.
Hope my question is clear enough for someone to help me come up with a
solution.
|