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!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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
53,384
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,200
Members
414,513
Latest member
junbuggle

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