Rename all worksheets except for one with specific name?

[albytross]

Hi,
I have this code which successfully renames my worksheets.
Its two parts:
1. first it adds underscores to the existing names
2. then it prefixes all worksheets with the value from cell C9.

How can i make the code skip both stages of renaming for a worksheet called "SKIP_ME"?
So that SKIP_ME is not renamed at all.

Sub RenameWorksheets()
Dim xWs As Worksheet

' Part one - add underscore to worksheet names
Sheets("Casing").Name = "_Cars"
Sheets("Collar").Name = "_Houses"
Sheets("DownholeSurvey").Name = "_Animals"

'Part two - adds cell value C9 as prefix to all worksheet names
 Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        sh.Name = Range("C9") & sh.Name
    Next sh
End Sub

 

[Micron]

For Each sh In ActiveWorkbook.Worksheets
   If sh.Name <> "SKIP_ME" Then
        sh.Name = Range("C9") & sh.Name
    Next sh

Some of that code doesn't seem to jive with what you claim. I'd say it doesn't prefix with an underscore, it changes the name (e.g. Casing) to something entirely different that just happens to start with an underscore. Also, that happens to only 3 sheets, but the loop could affect many more, or even fewer than 3 sheets.
Depending on what's up with that, it seems possible to do it all in one loop and pretty much eliminate the first part.

 

[albytross]

Thanks you're correct, it does rename the sheets. Thats ok, i just forgot to edit the VBA code here correctly.
How could i skip SKIP_ME in the renaming?

 

[Micron]

How could i skip SKIP_ME in the renaming?

Sorry, that's not what I said/meant. I mentioned doing whatever is needed in the loop and that's where you have skip_me. However, f there are only 4 sheets and you want to avoid just 1, why loop over 4 and try to avoid one particular sheet when you can have 3 lines like
Sheets("Casing").Name = Range("C9") & "_Cars"

- except I don't know where C9 is. Maybe on every renamed sheet?
The situation is a bit vague for me, but then I'm very literal (sometimes that's not a good thing but it's great for contract law!).

 

[albytross]

Hi I have a lot of sheets, I have just cut the code down for simplification to explain the question.

Here we have code that renames all the sheets shown using a loop function. I want the loop function to not rename one specific sheet, titled "skipme" to satisfy the example functionality.

Why?
Once sheets are renamed I have a CSV export macro. It exports all sheets, except for the sheet named SkipMe. If skipme is renamed, it will be exported and I don't want that.

 

[johnnyL]

How about:

Sub RenameWorksheets()
'
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> "SKIP_ME" Then sh.Name = Range("C9").Value & "_" & sh.Name
    Next
End Sub

 

[albytross]

How about:

Sub RenameWorksheets()
'
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> "SKIP_ME" Then sh.Name = Range("C9").Value & "_" & sh.Name
    Next
End Sub

Very clean syntax JohnnyL thanks!

Works perfectly.

 

[johnnyL]

Glad we could help.