Rename a sequence of sheets

[mhenk]

Hi there!

I have another stupid macro request.

I have 791 sheets, named "Table X.Y.Z", where X ranges from 1 to 4, Y ranges from 1 - 150, and Z ranges from 1-3.

I need to rename all of these sheets but nothing dramatic.

I need to change all the X's to X+3's.

So, every sheet titled "Table X.Y.Z" will become "Table X+3.Y.Z".

Obviously, this needs to start with the sheets labelled 4.Y.Z, otherwise there will be two workbooks with one name.

I have no problem sorting the sheets so that all the 4's come before all the 3's, etc, so a macro would work, but if someone could provide me some code to get started on renaming this block of sheets, it'd be much appreciated!

 

[Macropheliac]

Are there actually spaces in the names(Table 123) or are they spaceless(Table123)?

 

[mhenk]

THere are spaces and seperation periods:

Table 1.1.1
Table 1.2.1

etc.

 

[Macropheliac]

Thanks. I think I can manage this. I will post back.

 

[Norie]

meh

Have you considered copying the sheets to a new workbook and renaming them?

That might be easier than renaming them in the original.

Then again that approach could have implications if you have formulas referencing other worksheets.

PS 791 sheets?

 

[mhenk]

meh

Have you considered copying the sheets to a new workbook and renaming them?

That might be easier than renaming them in the original.

Then again that approach could have implications if you have formulas referencing other worksheets.

PS 791 sheets?

I'm not quite sure what you're getting at... something like moving all the Table 1's to one book, the table 2's to another book, then renaming them in the new books, and consolidating into the original?

That would work, but I'm still unclear as to how to write a macro to rename the sheets in the method I desire.

There are no formula's referencing other sheets, btw.

yeah, 791 sheets... so far. I'm probably going to be doubling that by the end of the day. Doesn't matter, no one looks at my work anyway . It'll just get printed and put in a big impressive looking binder

 

[Norie]

meh

That's not what I meant.

I mean just go through all the worksheets in the original workbook, copy them to a newly created workbook and change the name in the new workbook.

The reason I'm suggesting this is because that would/should help avoid any naming conflicts.

 

[mhenk]

YOu mean copy them one by one, changing the names as I go? No thanks!! lol...

 

[Macropheliac]

Try this:

Sub Change_Sheet_Names()
Dim Sht As Worksheet
Dim NumX As Long
Dim TheRest As String

For Each Sht In ThisWorkbook.Sheets
        NumX = Mid(Sht.Name, 7, 1)
        TheRest = Mid(Sht.Name, 8, 4)
        NumX = NumX + 3
        Sht.Name = "Table " & NumX & TheRest
Next Sht
End Sub

CAUTION: Try this on a dummy sheet first.

 

[Norie]

YOu mean copy them one by one, changing the names as I go? No thanks!! lol...

Eh, no.

I mean use code to cycle through all the sheets.

Then copy them to a newly created workbook, and then rename them.

Something along the lines of macropheliac's code, but hopefully avoiding any naming conflicts or having to sort the sheets.