Macro to Name Sheets by Tab Order

[WednesdayC]

Hi All

Is there a macro to name Worksheets by tab order?

If, for instance, I named the Worksheets Test 1, Test 2, Test 3 up to Test 52, I may need to move the order of Sheets, so for instance, if I moved Test 3 to Test 2 position, I would then like to be able to invoke a macro to rename all the sheets in sequential tabbed order.

Any help would be appreciated.

Regards
Wednesday

 

[Ruddles]

No there isn't, but I dare say one could be written...

Try this on a copy of your workbook:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Sub RenameSheets()[/FONT]
 
[FONT=Fixedsys]Const sRoot As String = "[COLOR=red]Test [/COLOR]"[/FONT]
 
[FONT=Fixedsys]Dim ws As Worksheet[/FONT]
[FONT=Fixedsys]Dim iSeq As Integer[/FONT]
 
[FONT=Fixedsys]iSeq = 0[/FONT]
 
[FONT=Fixedsys]For Each ws In ThisWorkbook.Worksheets[/FONT]
[FONT=Fixedsys]   iSeq = iSeq + 1[/FONT]
[FONT=Fixedsys]   ws.Name = CStr(iSeq)[/FONT]
[FONT=Fixedsys]Next ws[/FONT]
 
[FONT=Fixedsys]For Each ws In ThisWorkbook.Worksheets[/FONT]
[FONT=Fixedsys]   ws.Name = sRoot & ws.Name[/FONT]
[FONT=Fixedsys]Next ws[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

Set the value of sRoot as required.

This doesn't change the sheet's code name though.

 

[WednesdayC]

Hi Ruddles

Thank you very much once again (you helped me yesterday).

It works!!!!

Oh to be as smart as you.

Regards

Wednesday

 

[Ruddles]

Thank you very much once again (you helped me yesterday).

I remember!

Oh to be as smart as you.

Smart isn't knowing answers, it's knowing where to find them.