I have written the following code to print specific pages of a worksheet depending on which active worksheets are selected. Some worksheets have list tables which need to be filtered. When I run the printingSheets() procedure on its own it prints fine. When I call it in the before print procedure it prints the first workbook in the selection twice. Can someone explain this to me?
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call printingSheets
End Sub
Sub printingSheets()
Dim ws As Worksheet
Dim arySheets
Dim selSheets
Dim iSheet As Long
Dim i As Long
Set selSheets = ThisWorkbook.Windows(1).selectedSheets
i = 0
ReDim arySheets(selSheets.Count)
For Each ws In selSheets
arySheets(iSheet) = ws.Name
iSheet = iSheet + 1
With ws
If ws.Name = "12-32" Then
Worksheets("12-32").Activate
x = x + 1
ws.ListObjects("Table9").Range.AutoFilter Field:=1, Criteria1:= _
"<>"
ws.PageSetup.PrintArea = "$A$1:$J$249"
ws.PrintOut From:=1, to:=1, Copies:=1, Collate:=True, IgnorePrintAreas:=False
ws.PageSetup.PrintArea = "$A$1:$K$249"
ws.PrintOut From:=3, to:=3, Copies:=1, Collate:=True, IgnorePrintAreas:=False
ws.ListObjects("Table9").Range.AutoFilter Field:=1
Application.EnableEvents = True
Cancel = True
ElseIf ws.Name = "13-33" Then
Worksheets("13-33").Activate
x = x + 1
ws.ListObjects("Table8").Range.AutoFilter Field:=1, Criteria1:= _
"<>"
ws.PageSetup.PrintArea = "$A$1:$J$249"
ws.PrintOut From:=1, to:=1, Copies:=1, Collate:=True, IgnorePrintAreas:=False
ws.PageSetup.PrintArea = "$A$1:$K$249"
ws.PrintOut From:=3, to:=3, Copies:=1, Collate:=True, IgnorePrintAreas:=False
ws.ListObjects("Table8").Range.AutoFilter Field:=1
Application.EnableEvents = True
Cancel = True
ElseIf ws.Name = "Cap11-31" Then
Worksheets("Cap11-31").Activate
x = x + 1
ws.ListObjects("Table10").Range.AutoFilter Field:=1, Criteria1:= _
"<>"
ws.PageSetup.PrintArea = "$A$1:$J$249"
ws.PrintOut From:=1, to:=1, Copies:=1, Collate:=True, IgnorePrintAreas:=False
ws.PageSetup.PrintArea = "$A$1:$K$249"
ws.PrintOut From:=3, to:=3, Copies:=1, Collate:=True, IgnorePrintAreas:=False
ws.ListObjects("Table10").Range.AutoFilter Field:=1
Application.EnableEvents = True
Cancel = True
ElseIf ws.Name = "Cap10-30" Then
Worksheets("Cap10-30").Activate
x = x + 1
ws.ListObjects("Table11").Range.AutoFilter Field:=1, Criteria1:= _
"<>"
ws.PageSetup.PrintArea = "$A$1:$J$249"
ws.PrintOut From:=1, to:=1, Copies:=1, Collate:=True, IgnorePrintAreas:=False
ws.PageSetup.PrintArea = "$A$1:$K$249"
ws.PrintOut From:=3, to:=3, Copies:=1, Collate:=True, IgnorePrintAreas:=False
ws.ListObjects("Table11").Range.AutoFilter Field:=1
Application.EnableEvents = True
Cancel = True
Else
ws.PrintOut From:=1, to:=1, Copies:=1, Collate:=True, IgnorePrintAreas:=False
Application.EnableEvents = True
Cancel = True
End If
End With
Next ws
End Sub