VBA to apply macro to selected sheets

[Daystep]

Hello,

I've been dabbling with macros recently, but have not found a way to apply macros to selected sheets in the workbook. The macro I have works flawlessly on the active sheet, it applies the proper page breaks and if a certain section is completely hidden it won't print the page.

Below is the macro, and I am trying to get it to apply to any selected sheets in the workbook.

Sub ChangePrintArea()

Dim pb, i As Integer


pb = Array(31, 61, 109, 145, 193)




With ActiveSheet


    .ResetAllPageBreaks ' remove all page breaks from
    .PageSetup.PrintArea = "$A$1:$AI$192"
    .PageSetup.FitToPagesWide = 1
    .PageSetup.Zoom = 69
    For i = LBound(pb) To UBound(pb)
   
        If .Rows(pb(i)).Hidden = True Then
        .HPageBreaks.Add Before:=.Cells(749, 1)
        Else
        .HPageBreaks.Add Before:=.Cells(pb(i), 1)
        .HPageBreaks.Add Before:=.Cells(749, 1)
       
        End If
    Next i
End With
End Sub

Thanks,

 

[lemeister]

Your problem is that there can only be one ActiveSheet at a time. No matter how sheets you select, only one if them will be active. Therefore, you probably need to perform a loop through each of the selected worksheets. Something like:

For each ws In ActiveWindow.SelectedSheets
....your code....
Next ws