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

 

[Alex Blakenburg]

Correct me if I am wrong but the instructions look incorrect to me. It says after Alt+F8 to select This Workbook.
The way the code looks to me is that the workbook needing renaming of sheets has to be the active workbook when you run the macro.
This means for the purposes of that dialogue box that it is This Workbook and it does not have the macros in it.

The below isn't exactly how I would write it from scratch but moving forward from your code see if this is closer to what you are trying to do.

Sub Renamesheets_Revised()

' This renames the sheets
'
Dim SortC As Range
Dim AddMore As Range
Dim sht As Worksheet
Dim sheetName As String

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

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