VBA Macro not Applying to all pivot tables

[zgoda]

Hey guys, can you please help me with this simple problem?

I wanna know why this code is not working with all pivot tables in the file, and how can i make it work.

It is a code to block all pivot tables and hide the wizard.

Thanks!

This is the zipped file:

https://drive.google.com/file/d/1eeJy2YeUtMphYKJZ1z_qGvElxLah1Gvt/view?usp=sharing


This is the code:

Sub Inactive()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
  For Each pf In pt.PivotFields
      pf.EnableItemSelection = False
  Next pf
End Sub
Sub BLOCK()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
With ActiveSheet.PivotTables(1)
  .EnableWizard = False
  .EnableDrilldown = False
  .EnableFieldList = False
  .EnableFieldDialog = False
  .ShowPivotTableFieldList = False
  .PivotCache.EnableRefresh = False
  For Each pf In .PivotFields
    With pf
      .DragToPage = False
      .DragToRow = False
      .DragToColumn = False
      .DragToData = False
      .DragToHide = False
    End With
  Next pf
End With
End Sub

Sub UNBLOCK()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
With ActiveSheet.PivotTables(1)
  .EnableWizard = True
  .EnableDrilldown = True
  .EnableFieldList = True
  .EnableFieldDialog = True
  .PivotCache.EnableRefresh = True
  For Each pf In .PivotFields
    With pf
      .DragToPage = True
      .DragToRow = True
      .DragToColumn = True
      .DragToData = True
      .DragToHide = True
    End With
  Next pf
End With
End Sub
Sub active()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
  For Each pf In pt.PivotFields
      pf.EnableItemSelection = True
  Next pf
End Sub

 

[Jerry Sullivan]

Hi zgoda.

This reference in both procedures instructs VBA to just apply this to the first PivotTable in the active sheet.

ActiveSheet.PivotTables(1)

To process all Pivots in the ActiveSheet, use a For Each loop like this...

Dim pt as PivotTable

For Each pt in ActiveSheet.PivotTables
'....your process
'....

Next pt

To process all Pivots in all sheets in the ActiveWorkbook, use a second For Each loop like this...

Dim pt as PivotTable
Dim wks as Worksheet


For Each wks in ActiveWorkbook.Worksheets
   For Each pt in wks.PivotTables
      '....your process
      '....

   Next pt 
Next wks

 

[zgoda]

Thank a lot man.

I've encountered another problem, but with a little of logic i could overcome it.

in the bigger code we have periods, the period alone only work if a "With" comand is ruling it.

If im using a "For" Command, i have to insert the "Dim" reference of what im messing with.

In this case, before the dots in the bigger code, i had to insert pt before each period.

it stays like this:

Sub BLOCK()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
For Each pt In wks.PivotTables
With ActiveSheet.PivotTables(1)
  pt.EnableWizard = False
  pt.EnableDrilldown = False
  pt.EnableFieldList = False
  pt.EnableFieldDialog = False
  pt.PivotCache.EnableRefresh = False
  For Each pf In .PivotFields
    With pf
      .DragToPage = False
      .DragToRow = False
      .DragToColumn = False
      .DragToData = False
      .DragToHide = False
    End With
  Next pf
  End With
  Next pt
Next wks
End Sub

Hope this can help anyone in the future!!!

 

[Jerry Sullivan]

It's good that you figured out how to make that work.

If you are going to put pt in front of each reference, the With line is ignored. You could eliminate that....

For Each pt In wks.PivotTables
  pt.EnableWizard = False
  pt.EnableDrilldown = False
  pt.EnableFieldList = False
  pt.EnableFieldDialog = False
  pt.PivotCache.EnableRefresh = False
  For Each pf In [B][COLOR="#0000CD"]pt[/COLOR][/B].PivotFields
    With pf
      .DragToPage = False
      .DragToRow = False
      .DragToColumn = False
      .DragToData = False
      .DragToHide = False
    End With
  Next pf
Next pt

Or use pt as the With object...

For Each pt In wks.PivotTables
   With pt
     .EnableWizard = False
     .EnableDrilldown = False
     .EnableFieldList = False
     .EnableFieldDialog = False
     .PivotCache.EnableRefresh = False
     For Each pf In .PivotFields
       With pf
         .DragToPage = False
         .DragToRow = False
         .DragToColumn = False
         .DragToData = False
         .DragToHide = False
       End With
     Next pf
   End With
Next pt