Rename a sequence of sheets

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
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! :oops:
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
THere are spaces and seperation periods:

Table 1.1.1
Table 1.2.1

etc.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,299
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?:eek:
 

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
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?:eek:

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 :LOL:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,299
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jun 13, 2005
Messages
591
YOu mean copy them one by one, changing the names as I go? No thanks!! lol...
 

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
Try this:
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,299
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,457
Messages
5,658,875
Members
418,475
Latest member
ExcelBeginner233

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top