Tab Name change

[sujyo]

Hello All...I have a worksheet with Sheet1 to Sheet31 (tab). each tab named by date like 8-1-11 to 8-31-11. I make a copy of this worksheet and I want to change all 31 sheets names from 8-1-11-8-31-11 TO 9-1-11-9-30-11. Is there any way or formula, when I change first sheet(tab)'s name to 9-1-11 then rest of changes automatic....or ...or when I change 1st date then the rest of the date changes automatic.... Thanks in advance

 

[kchiba]

Hi Sujyo,


Try the following VBA code,


Note that I have a sheet called Main and strting from from A7 down I have the existing sheet names and in column B the new sheet names. When run the macro changes the sheet names


Sub Worksheet_Change()

Dim newSht As String

Dim oldSht As String

Dim wsOld As Worksheet

Dim wsNew As Worksheet

Dim Source As Range


Set Source = Worksheets("Main").Range("A7")

Do While Source <> ""

Source.Select

newSht = Source.Offset(0, 1)

extSht = Source.Text

On Error Resume Next

Sheets(extSht).Activate

If Err.Number = 0 Then 'sheetname already exists

Sheets(extSht).Activate

Sheets(extSht).Name = newSht

Exit Sub

End If

Sheets("Main").Select

Selection.Offset(1, 0).Select

Set Source = ActiveCell


Loop


End Sub

 

[sujyo]

Thanks a lot..