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

[lunicidal]

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!

 

[Asala42]

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.

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]

Doh brain freeze. An if statement would probably cover the new sheets come to think of it.

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

 

[Fluff]

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.

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]

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