Multiple Pivot tables from one filter VBA

c.barton

New Member
Joined
Aug 29, 2011
Messages
1
Hello, I have a problem that involves several pivot tables that all grip information from one document.

This document is updated daily and contains information on faults that have occurred to trucks; the faults can be mechanical and electrical, but I'm only interested in the electrical ones.

Unfortunately, the faults are not clearly defined as electrical or mechanical i.e.
the faults codes are listed as cables & wiring, brake accumulators etc. The problem herein is that there is roughly 140 different fault codes.

The idea is to get the first pivot table 'fault code' filter to duplicate for each of the subsequent tables on different worksheets.

I recorded a macro of changing the selection on the first pivot table and from here I linked this information to the other tables and it works. This code is entered below.

Code:
 If Sheets("Electrical Asset Performance").PivotTables("PVT1").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True Then
  Sheets("Terex MT4400").PivotTables("4400").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("Komatsu 960E").PivotTables("960").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("Letourneau L1850").PivotTables("1850").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("P&H 4100").PivotTables("4100").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("P&H 9020").PivotTables("9020").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("MT4400 Availability").PivotTables("4400D").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("MT4400 Availability").PivotTables("4400H").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("Komatsu 960E Availability").PivotTables("960D").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("Komatsu 960E Availability").PivotTables("960H").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("Letourneau L1850 Availability").PivotTables("1850D").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("Letourneau L1850 Availability").PivotTables("1850H").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("P&H 4100 Availability").PivotTables("4100H").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("P&H 4100 Availability").PivotTables("4100D").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("P&H 9020 Availability").PivotTables("9020H").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("P&H 9020 Availability").PivotTables("9020D").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("MTBF").PivotTables("4400M").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("MTBF").PivotTables("960M").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("MTBF").PivotTables("1850M").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("MTBF").PivotTables("4100M").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  Sheets("MTBF").PivotTables("9020M").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = True
  
End If

If Sheets("Electrical Asset Performance").PivotTables("PVT1").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False Then
  Sheets("Terex MT4400").PivotTables("4400").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("Komatsu 960E").PivotTables("960").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("Letourneau L1850").PivotTables("1850").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("P&H 4100").PivotTables("4100").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("P&H 9020").PivotTables("9020").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("MT4400 Availability").PivotTables("4400D").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("MT4400 Availability").PivotTables("4400H").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("Komatsu 960E Availability").PivotTables("960D").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("Komatsu 960E Availability").PivotTables("960H").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("Letourneau L1850 Availability").PivotTables("1850D").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("Letourneau L1850 Availability").PivotTables("1850H").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("P&H 4100 Availability").PivotTables("4100H").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("P&H 4100 Availability").PivotTables("4100D").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("P&H 9020 Availability").PivotTables("9020H").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("P&H 9020 Availability").PivotTables("9020D").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("MTBF").PivotTables("4400M").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("MTBF").PivotTables("960M").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("MTBF").PivotTables("1850M").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("MTBF").PivotTables("4100M").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  Sheets("MTBF").PivotTables("9020M").PivotFields("Reason").PivotItems("ACCIDENT DAMAGE").Visible = False
  
End If

End Sub

Obviously this cannot be the best way of doing it, as there would be 140 sections of code this size which would exceed the excel limits.

Is there a simple way of implementing this same code for 140 different entries? I'm brand new to VBA though I have some coding experience with C++ and matlab. I'm running excel 2007
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

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