Changing Tab Names VBA

[Tom98]

Hi,

If I wanted to go through each sheet and rename the tab names as the previous sheet plus 1, how would I go about doing this? Ie if I had sheet1, sheet2, sheet3 and sheet4, and then I were to delete sheet3 I would then like a macro to run to update the remaining sheets as sheet1, sheet2, sheet3. Any help with this would be great!

TIA

 

[mumps]

The problem is that if it starts at 1 and Sheets("Test_1") already exists, the macro will generate an error because you can't have two sheets with the same name.

 

[Tom98]

The problem is that if it starts at 1 and Sheets("Test_1") already exists, the macro will generate an error because you can't have two sheets with the same name.

Okay, there wouldn't be a way of telling it to skip over the sheets that I don't want it to consider so to start it counting at 1 and going from there?

Sorry for the hassle.

 

[mumps]

there wouldn't be a way of telling it to skip over the sheets that I don't want it to consider

Can you please explain in detail what you mean by this using a few examples?

 

[Tom98]

Can you please explain in detail what you mean by this using a few examples?

So currently I have:

|Front Cover | User guide | References | Button Sheet | Test_1 | Test_2 | Test_3 | Test_5 |

The solution you kindly provided produces:

|Front Cover | User guide | References | Button Sheet | Test_5 | Test_6 | Test_7 | Test_8 |

The solution I would ideally obtain:

|Front Cover | User guide | References | Button Sheet | Test_1 | Test_2 | Test_3 | Test_4 |

Is there a way of saying For each Worksheets in ThisWorkbook.Worksheets, if Sheets.Name<>"Test_*" then skip to next sheet? I don't know if this would work and don't know how to write it in vba exactly.

Cheers

 

[Tom98]

So currently I have:

|Front Cover | User guide | References | Button Sheet | Test_1 | Test_2 | Test_3 | Test_5 |

The solution you kindly provided produces:

|Front Cover | User guide | References | Button Sheet | Test_5 | Test_6 | Test_7 | Test_8 |

The solution I would ideally obtain:

|Front Cover | User guide | References | Button Sheet | Test_1 | Test_2 | Test_3 | Test_4 |

Is there a way of saying For each Worksheets in ThisWorkbook.Worksheets, if Sheets.Name<>"Test_*" then skip to next sheet? I don't know if this would work and don't know how to write it in vba exactly.

Cheers

I believe that changing it to
Sub ReNameSheets()
Application.ScreenUpdating = False
Dim x As Long
For x = ActiveSheet.Index + 1 To Sheets.Count
Sheets(x).Name = "Test_" & x - 4
Next x
Application.ScreenUpdating = True
End Sub

does the job.

Thanks for all your help

 

[mumps]

You are very welcome and well done.