Tab Names to Match Cell Values when Changed

  • Thread starter Thread starter Johnny
  • Start date Start date
J

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.
 
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!
 
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
'=================
 
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
 
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.
 
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
 
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.
 
Eduardo,

I got the same error when using your code regarding being able to rename a
sheet to the same name as another sheet.
 
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
'=================
 
Back
Top