none
Print Loops For Pivot Slicer (rough VBA code) RRS feed

  • Question

  • Basically this will be a macro to print all records for all slices in my workbook with one quick access button.

    I'm Using Office 2010 Pro and writing a macro to loop through selecting the slicer objects for each in pivot table and then run a series of print commands. I've figure out a lot I'll post the code but some things I'm not sure are right yet like setting a variable for sliceritems objects in the loop. I'll then be printing from several workbook sheets and one of those is also based on a variable I hope to pull to count how many pivot table rows are available for that slice to determine how many of the pages in a work book sheet with a variable number of pages of records. I think the code will make this much more clear as well as my commented out pseudo code with parts I'm not sure about yet I got the base code from doing a recorded macro. I'm not sure of the syntax for loops, variables, one variable will be a conditional of another variable so If code syntax as well may need to be cleaned up for that variable, and how I define a part of an object or an object to loop through for slicers?

    Sub PrintAllRecordsLoop()
    '
    ' PrintAllRecordsLoop Macro
    
    'Scrolls to pivot table tab column 1 Note in properties the code name of this sheet is Sheet13 which can be used instead in case it's renamed.
        Sheets("Single User Pivot").Select
        ActiveWindow.ScrollColumn = 1
        ActiveWindow.ScrollRow = 1
    
    ' This is where the first active loop should begin instead of selecting all records I would like to loop through selecting each one at a time.
    ' Then executing a print command based on the number of records in the pivot tabel for that person.
    '
    ' Something like this
    '
    ' Sub Select_Every_Slice_and_ Print()
    '
    '       Dim medCount As Integer
    '       medCount = ThisWorkbook.Worksheets("Sheet13").Range("D2").Value
    '            'D2 value = Where ever a countIf function is stored that has counted the rows of the pivot table.
    '            'Possibly use the id index value since every row in loaded for that slice should have one from the Db attached.
    '       Dim remainder As Long
    '       remainder = If(medCount MOD% 4 > 0) remainder = 1
    '       Dim pagesIncluded As Long
    '       pagesIncluded = (medCount \ 4) + remainder + 2
    '
    ' Dim slice As Object
    ' slice = (ActiveWorkbook.SlicerCaches("Slicer_FirstName").SlicerItems)
    ' For Each slice In ActiveWorkbook.SlicerCaches("Slicer_FirstName")
    '
    '       Sheets("Cover Sheet").Select
    '       ActiveWindow.ScrollColumn = 1
    '       ActiveWindow.ScrollRow = 1
    '       ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
    '       :=True, IgnorePrintAreas:=False
    '
    '       Sheets("8-hour MAR").Select
    '       ActiveWindow.ScrollColumn = 1
    '       ActiveWindow.ScrollRow = 1
    '       ActiveWindow.SelectedSheets.PrintOut From:=1, To:=pagesIncluded, Copies:=1, Collate _
    '       :=True, IgnorePrintAreas:=False
    ' Next slice
    '
    ' End Sub
    
    
        ActiveWorkbook.SlicerCaches("Slicer_FirstName").VisibleSlicerItemsList = Array _
            ( _
            "[PPIsheets].[FirstName].&[Jordan]")
            'replace [Jordan] with a variable to loop through all slicer
            
            Sheets("Cover Sheet").Select
            ActiveWindow.ScrollColumn = 1
            ActiveWindow.ScrollRow = 1
            ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
            :=True, IgnorePrintAreas:=False
            
            Sheets("8-hour MAR").Select
            ActiveWindow.ScrollColumn = 1
            ActiveWindow.ScrollRow = 1
            ActiveWindow.SelectedSheets.PrintOut From:=1, To:=10, Copies:=1, Collate _
            :=True, IgnorePrintAreas:=False
            'Here I would like to first put in a count if command to define the 'To:#'
            'I would count the number of records on the pivot table divided by four for each page.
            'Plus 4 for an additional blank page to be printed or plus 8 so on round up to a whoel number for the # variable
            'PrintOut From:=1, To:=#
            Dim medCount As Integer
            medCount = ThisWorkbook.Worksheets("Sheet13").Range("D2").Value
            Dim pages As Long
            pages = (medCount / 4) + 2
            'try using the modulus % operator to round up to whole intergers before adding 1 to the page count.
            '?/? Operator ? This operator performs division i.e. 5/2 = 2.5
            '?\? Operator ? This operator performs division and rounds the final number down to the nearest integer. i.e. 5\2=2
            '?MOD? operator ? This operator performs division and returns the remainder of the division. i.e. 5 MOD 2 = 1
            
    'This is a separate logic I was trying out this selects the check boxes in a pivot table column to bring up all of those records together.
            Sheets("Single User Pivot").Select
            ActiveWindow.ScrollColumn = 1
            ActiveWindow.ScrollRow = 1
    
            ActiveWorkbook.SlicerCaches("Slicer_FirstName").VisibleSlicerItemsList = Array _
            ( _
            "[PPIsheets].[FirstName].&[Born]")
            
            ActiveWorkbook.SlicerCaches("Slicer_FirstName").VisibleSlicerItemsList = Array _
            ( _
            "[PPIsheets].[FirstName].&[Jordan]")
        
        
            Sheets("Single User Pivot").Select
            ActiveWindow.ScrollColumn = 1
            ActiveWindow.ScrollRow = 1
            
            ActiveSheet.PivotTables("PivotTable3").PivotFields( _
            "[PPIsheets].[FirstName].[FirstName]").VisibleItemsList = Array( _
            "[PPIsheets].[FirstName].&[Born]", "[PPIsheets].[FirstName].&[Jack]")

    Saturday, August 31, 2019 9:32 AM

All replies

  • Hi,

    Your issue is more related about vba,and this forum is discussing and asking questions about the vb.net,ask in the following forum.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=isvvba

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 2, 2019 1:45 AM