VBA- Selecting sheets listed in a named range

the_steve

New Member
Joined
Oct 25, 2011
Messages
2
(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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This will select each sheet in the list sequentially:
Code:
Sub SelectSheetsInList()
For Each sh In Range("Sheet_List")
    Sheets(sh.Value).Select
Next sh
End Sub
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Or maybe

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

the_steve

New Member
Joined
Oct 25, 2011
Messages
2
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!



Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try this:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,137
Messages
5,599,945
Members
414,350
Latest member
ajesh27

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top