VBA to Select All Visible Sheets

[Guest]

Hi ....

I need to write VBA code that will select all visible sheets in the
workbook, and then change the page setup settings. Is there a simple VBA
command to select all visible sheets or do I need to use a variation of the
following:

Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Select

- in which case I will need to loop through the workbook names and build
the string since I don't know the sheet names. There are approx 40 sheets in
each file.

Thanks for your assistance ....

 

[Gary Keramidas]

this would select all of the sheets

Worksheets.Select

 

[Tom Ogilvy]

You would need to do page setup on each individual sheet. Grouping isn't
really supported in VBA.


You could set up one sheet, then group the sheets and use this method posted
by KeepItCool

sheets(array("sheet2","sheet3","sheet4")).select
sheets("sheet3").activate
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show


You can add code to select all visible sheets:

Sheet3 has been formatted - it is the master sheet.

Sub SelectVisibleSheets()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Visible Then
ws.Select Replace:=False
End If
Next ws
sheets("sheet3").activate
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show
End Sub

 

[Guest]

Thanks, Gary. I thought this might solve the problem, but on activating one
sheet after selecting all of them, the change page setting code only changed
the settings on that one activated sheet, even though all sheets were
selected - it did not change settings for all of the selected sheets, like it
would if you made the change manually (interactively).

 

[Guest]

The orignal code did loop through each worksheet and did a page setup
operation on each sheet. Due to the signifiant number of workbooks and
worksheets inside each workbook, this code took much longer to make the
changes than doing it manually (by manually selecting all sheets and doing
the page setup change).


I'm going to experiment with the array syntax below - that will probably due
the trick. Thanks for your help ...

b.