VBA- Selecting sheets listed in a named range

[the_steve]

(Sorry if this has been covered.)

I have a list of sheet names in the named range "Sheet_List". How can I select these sheets in VBA based on that range?

Note: The workbook is a bunch of summary sheets. I want to format the sheets denoted in the named range for printing in a few different ways.

 

[JoeMo]

This will select each sheet in the list sequentially:

Sub SelectSheetsInList()
For Each sh In Range("Sheet_List")
    Sheets(sh.Value).Select
Next sh
End Sub

 

[VoG]

Or maybe

Sub SelectSheetsInList()
For Each sh In Range("Sheet_List")
    Sheets(sh.Value).Select Replace:=False
Next sh
End Sub

 

[the_steve]

Thanks for the responses. I have attached a copy of the macro that I am currently using.

Is there any way to accomplish this without having to format each sheet individually? I'm running this on 30+ sheets and my current macro takes over a minute to complete.

Thanks Much!

Sub Format_PrintList_For_Printing()

   Application.ScreenUpdating = False
   
   Application.Calculation = xlCalculationManual
    
    For Each x In Range("PrintList")
        
        Sheets(x.Value).Rows("32:33").EntireRow.Hidden = True
        
        With Sheets(x.Value).PageSetup
            
            .PrintArea = "$A$1:$U$55"
            .CenterHorizontally = True
            .PaperSize = xlPaperLetter
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .Orientation = xlLandscape
            
        End With
        
    Next x
    
    Application.ScreenUpdating = True
    
    Application.Calculation = xlCalculationAutomatic   

End Sub

 

[JoeMo]

Try this:

Sub SelectSheetsInList()
For Each sh In Range("Sheet_List")
    Sheets(sh.Text).Select Replace:=False
Next sh
For Each sh In ActiveWindow.SelectedSheets
    sh.Rows("32:33").Hidden = True
    sh.PrintArea = "$A$1:$U$55"
    sh.CenterHorizontally = True
    sh.PaperSize = xlPaperLetter
    sh.FitToPagesWide = 1
    sh.FitToPagesTall = 1
    sh.Orientation = xlLandscape
Next sh
End Sub