Format multiple sheets in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If I turn on the macro recorder, then select sheets 3 thru 10 (group), then go to file, page setup, and make the changes I desire, the changes are applied to all sheets selected. When I run the VBA code I just recorded, it only formats the active sheet. What am I doing wrong?

Thanks, Dave

Excel XP
Win XP
 
I did as you described and this is what I found (Win 2000, XL 2000):

The original recording did not work, as you said:

Sub test() 'this doesn't work
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Range("A1").Select
Selection.Font.Bold = True
End Sub

However, eliminating the second line made it work as you'd want:

Sub test() 'this works
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Range("A1").Select
Selection.Font.Bold = True
End Sub

Strangely, if I try to condense it further to avoid selecting a range, it
doesn't work again:

Sub test() 'this doesn't work
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Range("A1").Font.Bold = True
End Sub

Ultimately, will probably want to look at formatting a collection of sheets,
using the for-each construct, e.g.,

Sub test()

Dim sh As Worksheet

For Each sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
sh.Range("A1").Font.Bold = True
Next sh

End Sub

hth (at least a little),

Doug

DrDave1958 said:
If I turn on the macro recorder, then select sheets 3 thru 10 (group),
Click to expand...
then go to file, page setup, and make the changes I desire, the changes are
applied to all sheets selected. When I run the VBA code I just recorded, it
only formats the active sheet. What am I doing wrong?
 
Most grouping activities are not supported in VBA. As Doug has discovered,
this can sometimes be overcome by selecting a cell - however this has no
application to pagesetup. For page setup, you have to loop through the
sheets and make your settings on each one.

for each sh in Sheets("Array("sheet2", "sheet3", "sheet4")
With sh.PageSetup
.LeftFooter = "ABCD"
' etc
End With
Next

A trick recently posted by KeepItcool is to format one sheet, then transmit
this to the remaining

With Sheets("Sheet3").PageSetup
.LeftFooter = "ABCD"
'etc
End with
sheets(array("sheet2","sheet3","sheet4")).select
sheets("sheet3").activate
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show
Sheets("Sheet3").Select 'ungroups the sheets
--
Regards,
Tom Ogilvy



DrDave1958 said:
If I turn on the macro recorder, then select sheets 3 thru 10 (group),
Click to expand...
then go to file, page setup, and make the changes I desire, the changes are
applied to all sheets selected. When I run the VBA code I just recorded, it
only formats the active sheet. What am I doing wrong?
 
Back
Top