R
reigh7
Guest
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]")
Continue reading...
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]")
Continue reading...