Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Do not print using VBA (Excel 2002)

    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Do not print using VBA (Excel 2002)

    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

  3. #3
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do not print using VBA (Excel 2002)

    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.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Do not print using VBA (Excel 2002)

    Does this do what you want?
    Steve

    <pre>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</pre>


  5. #5
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do not print using VBA (Excel 2002)

    Thank You, it works perfect.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •