VBA Macro not Applying to all pivot tables

zgoda

New Member
Joined
Feb 10, 2016
Messages
14
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:

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
 
Last edited:

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi zgoda.

This reference in both procedures instructs VBA to just apply this to the first PivotTable in the active sheet.
Code:
ActiveSheet.PivotTables(1)

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

Code:
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...

Code:
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

New Member
Joined
Feb 10, 2016
Messages
14
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:

Code:
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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....
Code:
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...
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,940
Messages
5,598,975
Members
414,270
Latest member
skipolmsted

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top