Do not print using VBA (Excel 2002)

Topic

New Reply

I have an Excel sheet I post every week and it has a pivot table report in it. I have a Marco that cycles all of the data within the pivot table and prints a page for each selection. I have posted the code for it. I was wondering if there was a way to skip a page if there was no data for that selection to print?

Reply | Quote

Replies

I am a little confused on your setup and exactly what you are asking. Could you post a sample file so we could get a better understanding of your setup?

Delete or change any proprietary info…

Steve

Reply | Quote

I was thinking while at lunch that I did not make my self very clear.

A macro was written a few years ago for me that would select and print each block/line then each shift in the pivot table. The macro would cycle through all of the data. Even though something was selected, there might not be any data in the detail section of the pivot table. I would like to skip the printing of the ones with out the data.

My VBA skill for Excel is lacking, as I do not know the proper way to check for the data in the detail section of the pivot table. I was hoping for a push in the correct direction or an example of how to do this.

I attached an example of the pivot table with made up data as requested too.

Reply | Quote

Does this do what you want?
Steve

Sub PrintBlock(BlockName As String)
    With ActiveSheet.PivotTables("BlockShift")
        .PivotFields("Block/Line").CurrentPage = BlockName
        If (BlockName = "QH01" Or BlockName = "Materials") Then
            .PivotFields("Shift").CurrentPage = "(All)"
            Dim a
            If .GetPivotData("Sum of Qty -1")  0 Then
                ActiveWindow.SelectedSheets.PrintOut Copies:=1
            End If
        Else
            .PivotFields("Shift").CurrentPage = "1"
            If .GetPivotData("Sum of Qty -1")  0 Then
                ActiveWindow.SelectedSheets.PrintOut Copies:=1
            End If
    
            .PivotFields("Shift").CurrentPage = "2"
            If .GetPivotData("Sum of Qty -1")  0 Then
                ActiveWindow.SelectedSheets.PrintOut Copies:=1
            End If
    
            If (BlockName = "BI" Or BlockName = "BD04" Or BlockName = "TRPR" _
                Or BlockName = "PRSS" Or BlockName = "200 TON" Or _
                BlockName = "200 TONU" Or BlockName = "400 TON") Then
                    .PivotFields("Shift").CurrentPage = "3"
                If .GetPivotData("Sum of Qty -1")  0 Then
                    ActiveWindow.SelectedSheets.PrintOut Copies:=1
                End If
            End If
        End If
    End With
End Sub

Reply | Quote

Thank You, it works perfect.

Reply | Quote