vba Worksheet Before Print Prints Twice

344 views Asked by At

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
0

There are 0 answers