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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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
 

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,448
Office Version
  1. 365
Platform
  1. Windows
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
 

lunicidal

New Member
Joined
Feb 13, 2011
Messages
7
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,673
Messages
5,838,710
Members
430,566
Latest member
ChanchalSingh

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