Hi Johnny,
did you try my code that works, as it rename again each sheet, attach a code
to a button so you can run it from there

"Johnny" wrote:

Your assumptions are correct. The code worked fine up until I typed in
another date in A1 and got a run-time error that told me that I could not
rename a sheet to the same name as another sheet. I understand the problem
but need help on a solution.

For example, if I change the date in A1 from 2009 to 2010, a conflict
occurs when the code tries to rename the TAB labeled 2009 to 2010 because the
next worksheet in the sequence already has that name.


"Luke M" wrote:

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.