Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Select All Visible Sheets
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 .... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Select All Visible Sheets
this would select all of the sheets
Worksheets.Select -- Gary "bill_morgan" wrote in message ... 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 .... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Select All Visible Sheets
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 -- Regards, Tom Ogilvy "bill_morgan" wrote in message ... 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 .... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Select All Visible Sheets
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). "Gary Keramidas" wrote: this would select all of the sheets Worksheets.Select -- Gary "bill_morgan" wrote in message ... 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 .... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Select All Visible Sheets
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. "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "bill_morgan" wrote in message ... 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 .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I select, cut, and paste visible cells only | Excel Discussion (Misc queries) | |||
Select Visible sheets | Excel Programming | |||
Select Visible Cells Only | Excel Discussion (Misc queries) | |||
Select visible cells using vba | Excel Programming | |||
select visible cells when printing | Excel Programming |