Need loop to only rename sheets that are newly inserted starting with sheet2...

lunicidal

New Member
Joined
Feb 13, 2011
Messages
7
Hi There,

I have a large work book and continually add new sheets which are renamed using this code:

Sub RenameSheet()


Dim rs As Worksheet


For Each rs In Sheets
rs.Name = rs.Range("B9")
Next rs


End Sub

But it takes ages as it have more than 20 sheets and goes through them all.

As every sheet I add before I run the code is named:

sheet2, sheet3, sheet4 and so on, can someone help me to wrap a loop around this code to only rename the newly inserted sheets?

Thanks!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I'm not sure how you'd identify newly inserted sheets, but that aside I have to wonder why that code would take so long. Is the spreadsheet recalculating every time a sheet is renamed? You may want to just test something along these lines to see if the code takes less time.

Code:
Application.ScreenUpdating = False
Application.Calculation = xlManual

For Each rs In Sheets
 rs.Name = rs.Range("B9")
 Next rs

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Calculate
 
Upvote 0
Doh brain freeze. An if statement would probably cover the new sheets come to think of it.

Code:
For Each rs In Sheets
 If Left(rs.Name, 5) = "Sheet" Then
     rs.Name = rs.Range("B9")
 End If
 Next rs
 End If
 
Upvote 0
You may want to just test something along these lines to see if the code takes less time.
I'd also recommend turning of EnableEvents, in-case there are any worksheet events.
Code:
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.EnableEvents = False

 For Each rs In Sheets
 rs.Name = rs.Range("B9")
 Next rs

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Calculate
 
Upvote 0
Thanks Asala42 and Fluff.

One of my sheets has a whole bunch of INDIRECT and another one with some google map lookups. It's the dynamic stuff that was slowing it down. I need to figure out how to solve that....(another post)

This is the final code I used. Thanks for your help!

Sub Sheetfix()

Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.EnableEvents = False


Dim rs As Worksheet


For Each rs In Sheets
If Left(rs.Name, 5) = "Sheet" Then
rs.Name = rs.Range("B9")
End If
Next rs


Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Calculate


End Sub
 
Upvote 0

Forum statistics

Threads
1,211,685
Messages
6,103,291
Members
447,853
Latest member
olddutch7

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
Back
Top