Renaming Sheets with multiple ranges for the name

[Lukeace5]

Hi All,
I have this code that works great but when I try to add another range to it, it just does not work.
Could you please assist?
thanks heaps

Set SortC = Workbooks("Rename Sheets Macro").Sheets("Macro").Range("B2")
Set AddMore = Workbooks("Rename Sheets Macro").Sheets("Macro").Range("C2").Value <- NEW RANGE TO BE ADDED TO SHEETNAME

Worksheets(1).Activate
Range(SortC).Select
sheetName = ActiveSheet.Range(SortC).Value
ActiveSheet.Name = sheetName
With ActiveWindow
.SplitRow = 9
End With
With ActiveSheet.PageSetup
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.Order = xlDownThenOver
.PrintTitleRows = "$1:$9"
.Zoom = False
.FitToPagesTall = 200
.FitToPagesWide = 1
End With

 

[Lukeace5]

Sorry, I did not put the code in the VBA code area

Set SortC = Workbooks("Rename Sheets Macro").Sheets("Macro").Range("B2")
Set AddMore = Workbooks("Rename Sheets Macro").Sheets("Macro").Range("C2").Value

    Worksheets(1).Activate
    Range(SortC).Select
    sheetName = ActiveSheet.Range(SortC).Value
    ActiveSheet.Name = sheetName
    With ActiveWindow
    .SplitRow = 9
    End With
    With ActiveSheet.PageSetup
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .PaperSize = xlPaperA4
        .Order = xlDownThenOver
        .PrintTitleRows = "$1:$9"
        .Zoom = False
        .FitToPagesTall = 200
        .FitToPagesWide = 1
    End With

 

[Alex Blakenburg]

The code you have doesn't seem to make sense. Are you trying to combine 2 print areas ?
If so what are the print areas, I am assuming it is not just B2 & C2 ?

 

[Lukeace5]

The code you have doesn't seem to make sense. Are you trying to combine 2 print areas ?
If so what are the print areas, I am assuming it is not just B2 & C2 ?

Hi Alex,
Thanks for replying.
It is a macro that renames the sheets based on a cell reference.
They are budget sheets and the cost center is in B2 and I would also like to put "GL" (written in cell C2) on the end so it says "988710 GL" for example.
It currently works renaming the sheets to the cost center, but I tried to add the GL part so I don't have to do them by hand on each sheet.
I have tried just putting "& range(c2).value on the end, and a heap of other things including activesheet, workbook, etc but I keep getting the End of statement error.
Something similar worked in another of my spreadsheets but does not in this one.
Thanks
Luke

 

[Alex Blakenburg]

I'm afraid that is still not very clear.
Are you saying you have multiple sheets and each sheet has the cost centre in B2 which matches the sheet name ?
And that each sheet also has a GL in C2 and you want to change the sheet name to add the GL account to the end.

Does this apply to every to every sheet or are there sheets to be excluded ?

Show me what you have in B2 & C2 and how you want the sheet name to look.

 

[jolivanes]

Sub Maybe_So()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Name = sh.Name & sh.Range("C2").Value
Next sh
End Sub

or

Sub Or_Maybe_So()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Name = sh.Range("B2").Value & sh.Range("C2").Value
Next sh
End Sub

 

[Lukeace5]

Sub Maybe_So()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Name = sh.Name & sh.Range("C2").Value
Next sh
End Sub

or

Sub Or_Maybe_So()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Name = sh.Range("B2").Value & sh.Range("C2").Value
Next sh
End Sub

Thanks Jolivanes, this would work fine, however, I have the ranges the macro needs to look for in the macro sheet, and the sheets it runs the macro in is another sheet.
Let me try and explain better.
I run a report that has generic sheet names, then I open my macro workbook and change the values in B2 (the cell in the report that has the cost centre name) and C2 (the additional info to be added after the cost centre name), go back to the report and press Alt+F8 and run the macro from my macro workbook.
This is why I have the
Set SortC = Workbooks("Rename Sheets Macro").Sheets("Macro").Range("B2")
Set AddMore = Workbooks("Rename Sheets Macro").Sheets("Macro").Range("C2").Value
in the top, however, when I add them to your code, is still not work. I think I am not defining the Cell range in B2 correctly compared to just extra words to be added from C2.
If you could help further I would greatly appreciate it.
Thanks

 

[Alex Blakenburg]

I am still very unclear as to what you are trying to do.
For each sh doesn't make sense in the context of using the same values (SortC & AddMore) for each iteration ie each sheet in the workbook.

Can you show us a image of the sheet tabs in the workbook you are trying to rename before changing the names.
In the macro workbook is there a list of values starting at B2 & C2 or is it really only B2 & C2 that contains values.
Can you show us the tabs after the renaming ?

Just based on your description you only seem to be renaming a single sheet being the activesheet of a different workbook to the one containing the macro.
Note: ThisWorkbook refers to the workbook containing the macro

Sub RenameSht()
    Dim SortC As Range
    Dim AddMore As Range

    With ThisWorkbook.Sheets("Macro")
        Set SortC = .Range("B2")
        Set AddMore = .Range("C2")
    End With
    
    ActiveSheet.Name = SortC.Value & " " & AddMore.Value    
End Sub

 

[Lukeace5]

I am still very unclear as to what you are trying to do.
For each sh doesn't make sense in the context of using the same values (SortC & AddMore) for each iteration ie each sheet in the workbook.

Can you show us a image of the sheet tabs in the workbook you are trying to rename before changing the names.
In the macro workbook is there a list of values starting at B2 & C2 or is it really only B2 & C2 that contains values.
Can you show us the tabs after the renaming ?

Just based on your description you only seem to be renaming a single sheet being the activesheet of a different workbook to the one containing the macro.
Note: ThisWorkbook refers to the workbook containing the macro

Sub RenameSht()
    Dim SortC As Range
    Dim AddMore As Range

    With ThisWorkbook.Sheets("Macro")
        Set SortC = .Range("B2")
        Set AddMore = .Range("C2")
    End With
   
    ActiveSheet.Name = SortC.Value & " " & AddMore.Value   
End Sub

Hi Alex, Thanks heaps for your help.
You are nearly there, the picture attached is of my macro workbook, that contains the macro. It points to the other sheet that, it does not matter what the sheet names are called, they will be renamed to whatever is in the cells that I enter into on my macro sheet, as they all have almost the same info but lots of budget or payroll or anything info, but the cost centre or identifying name of the group in the same cell on every sheet.
So, there will be multiple sheets for say multiple departments, that all have a different cost centre for each one (eg: 988100 & 988200), and each sheet has the cost centre sitting in cell B10 for every sheet.
I run the macro and then each sheet will have the different cost centre number from cell B10 and then what info I want such as GL (General Ledger), so they look like "988100 GL" & "988200 GL".
Does this make it clearer??
Sorry if I am not explaining it properly.
Thanks

 

[Lukeace5]

Hi Alex,
Thanks heaps, your Dim and Set worked and the "ActiveSheet.Name = SortC.Value & " " & AddMore.Value" worked. I added it to my original code and now both the cost centre and the GL show up on the sheet name.
I know it is dumb having code for each sheet but it works
Now I can use this for any sheet, such as payroll reports that also have multiple sheets and cost centres and label them "CC Payroll" or whatever.
Thanks heaps all

Sub Renamesheets()

' This renames the sheets
'
Dim SortC As Range
Dim AddMore As Range
Set SortC = Workbooks("Rename Sheets Macro").Sheets("Macro").Range("B2")
Set AddMore = Workbooks("Rename Sheets Macro").Sheets("Macro").Range("C2")

    Worksheets(1).Activate
    Range(SortC).Select
    sheetName = ActiveSheet.Range(SortC).Value & " " & AddMore.Value
    ActiveSheet.Name = sheetName
    With ActiveWindow
    .SplitRow = 9
    End With
    With ActiveSheet.PageSetup
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .PaperSize = xlPaperA4
        .Order = xlDownThenOver
        .PrintTitleRows = "$1:$9"
        .Zoom = False
        .FitToPagesTall = 200
        .FitToPagesWide = 1
    End With

    Worksheets(2).Activate
    Range(SortC).Select
    sheetName = ActiveSheet.Range(SortC).Value & " " & AddMore.Value
    ActiveSheet.Name = sheetName
    With ActiveWindow
    .SplitRow = 9
    End With
    With ActiveSheet.PageSetup
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .PaperSize = xlPaperA4
        .Order = xlDownThenOver
        .PrintTitleRows = "$1:$9"
        .Zoom = False
        .FitToPagesTall = 200
        .FitToPagesWide = 1
    End With
    
    Worksheets(3).Activate
    'etc etc etc for 50 odd sheets'