Counting the # of pivot tables within a work book and passing in as a variable
 

(Thanks to Tom who helped me loop through each pivot table!)

How would one change the macro so that instead of "30" it ran for the total # of pivot tables in the workbook?

Sub Example()

Dim i As Integer

For i = 1 To 30

Worksheets(i).Activate

ActiveSheet.PivotTables(1).PivotSelect "", xlDataAndLabel
Range("A6").Select
ActiveSheet.PivotTables(1).PivotSelect "PLU", xlButton
With ActiveSheet.PivotTables(1).PivotFields("PLU")
.AutoSort xlDescending, " TYSaleQty"
.AutoShow xlAutomatic, xlTop, 50, " TYSaleQty"
End With
ActiveSheet.PivotTables(1).PivotSelect "", xlDataAndLabel
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$6"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold""&14&A"
.CenterHeader = "&""Arial,Bold""&14Sales To Inventory Report"
.RightHeader = "&""Arial,Bold""&14&F"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Next i

End Sub

Counting the # of pivot tables within a work book and passing in as a variable
 

Please ignore, my older newspost answered this.

Alan N
"AlanN" wrote in message ...
(Thanks to Tom who helped me loop through each pivot table!)

How would one change the macro so that instead of "30" it ran for the total # of pivot tables in the workbook?

Sub Example()

Dim i As Integer

For i = 1 To 30

Worksheets(i).Activate

ActiveSheet.PivotTables(1).PivotSelect "", xlDataAndLabel
Range("A6").Select
ActiveSheet.PivotTables(1).PivotSelect "PLU", xlButton
With ActiveSheet.PivotTables(1).PivotFields("PLU")
.AutoSort xlDescending, " TYSaleQty"
.AutoShow xlAutomatic, xlTop, 50, " TYSaleQty"
End With
ActiveSheet.PivotTables(1).PivotSelect "", xlDataAndLabel
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$6"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold""&14&A"
.CenterHeader = "&""Arial,Bold""&14Sales To Inventory Report"
.RightHeader = "&""Arial,Bold""&14&F"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Next i

End Sub