Tab Names to Match Cell Values when Changed

[Johnny]

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.

 

[Eduardo]

Hi,
try

Option Explicit
Sub testme()

Dim iRow as long
with activesheet
for irow = 1 to .cells(.rows.count,"A").end(xlup).row
sheets(irow).name = .cells(irow,"A").value
next irow
end with

End with



This could fail if you have invalid names in column A and/or there are
duplicate
names or sheets with the same name already existing. Or if you don't have
enough sheets!

 

[Luke M]

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
'=================

 

[Eduardo]

opps, use this

Maybe something like:

Option Explicit
Sub testme()

Dim iRow as long
with activesheet
for irow = 2 to .cells(.rows.count,"A").end(xlup).row
sheets(irow).name = .cells(irow,"A").value
next irow
end with

End Sub

 

[Johnny]

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.

 

[Eduardo]

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]

Yes I did but didn't get it to work. I am going to try it again and see if I
can figure it out. I'll get back to you.

 

[Johnny]

Eduardo,

I got the same error when using your code regarding being able to rename a
sheet to the same name as another sheet.

 

[Luke M]

We can get around that by temporarily renaming each sheet to something else.

'===========
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 = ws.Name & "PLACEHOLDER"
i = i + 1
End If
If i > 4 Then Exit For
Next

i = 1
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 For
End If
Next

End Sub
'=================