VBA code to select multiple pivot tables

[Memanja15]

Hi all.

I'm hoping somebody can help please.

I've recorded a macro for one of my pivot tables, but I would like this recording to be applied to 3 other pivot tables in the same sheet.

At the moment, my code only references a range (A1:J14) and not the pivot table (PivotTable5) - can anybody help please? Code pasted below:

Sub Macro3()
'
' Macro3 Macro
'


'
ActiveCell.Offset(-1, -2).Range("A1:J14").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -4.99893185216834E-02
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -4.99893185216834E-02
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -4.99893185216834E-02
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -4.99893185216834E-02
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -4.99893185216834E-02
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -4.99893185216834E-02
.Weight = xlThin
End With
With Selection
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.Select
Selection.Font.Bold = True
ActiveCell.Offset(0, 3).Columns("A:E").EntireColumn.Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
ActiveSheet.PivotTables("PivotTable5").PivotSelect "'Policy #1 '", _
xlDataAndLabel, True
End Sub

 

[DanteAmor]

Try this

You must have, in all tables, a field called: "Policy #1 " note: after 1 has a blank

Sub select_multiple_pivot_tables()
  Dim pt As PivotTable
  For Each pt In ActiveSheet.PivotTables
    pt.PivotSelect "'Policy [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  '", xlDataAndLabel, True
    With Range(pt.TableRange1.Address)
      .Borders.LineStyle = xlContinuous
      .Borders.ThemeColor = 1
      .Borders.TintAndShade = -4.99893185216834E-02
      .Borders.Weight = xlThin
      .WrapText = True
      .Orientation = 0
      .AddIndent = False
      .VerticalAlignment = xlCenter
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = False
      .Columns(1).Font.Bold = True
    End With
  Next
End Sub

 

[Memanja15]

Try this

You must have, in all tables, a field called: "Policy #1 " note: after 1 has a blank

Sub select_multiple_pivot_tables()
  Dim pt As PivotTable
  For Each pt In ActiveSheet.PivotTables
    pt.PivotSelect "'Policy [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL]  '", xlDataAndLabel, True
    With Range(pt.TableRange1.Address)
      .Borders.LineStyle = xlContinuous
      .Borders.ThemeColor = 1
      .Borders.TintAndShade = -4.99893185216834E-02
      .Borders.Weight = xlThin
      .WrapText = True
      .Orientation = 0
      .AddIndent = False
      .VerticalAlignment = xlCenter
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = False
      .Columns(1).Font.Bold = True
    End With
  Next
End Sub

Hi thank you for this. Where do I enter Policy 1? Is this just a column heading?

 

[DanteAmor]

Hi thank you for this. Where do I enter Policy 1? Is this just a column heading?

I do not understand what you mean.
The "Policy 1 " data is in your original macro.

Or remove this line from macro: pt.PivotSelect "'Policy #1 '", xlDataAndLabel, True
And try again.