Using Macro to Alter Multiple Pivot Tables

[Guest]

I recently posted a question about "...how to use a macro to alter a Pivot
Tabel". Debrah gave me a sollution which worked perfectly :-D

Though I wanted more... I tried to alter two Pivot Tables at the same time.
And it worked splenditly! :-D

Thus I wanted to extend the borders. Can Anybody tell me wheter it is
possible to make a Macro which alters the Pivot tables ONLY if the exact
match is made with the data of the Pivot Table and the data filled in in the
reference cell.

The problem is that I use two different databases to fill two different
pivot tables; the one thing that connexts them are the "ARF numbers". It
happens that there is data about the ARF in only one of the two databases.

Right now I use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$20" Then
Sheets("INFO Dbase Uren").PivotTables("INFO Dbase Uren") _
.PivotFields("ARF No.").CurrentPage = Target.Value
Sheets("INFO DBase Materiaal Kosten").PivotTables("INFO DBase Materiaal
Kosten") _
.PivotFields("Reference.").CurrentPage = Target.Value
End If
End Sub

Concrete Question: "If one of the Databases does not contain the requested
Data; can it set the Pivot Table connected to that Database to "Blank"?

I hope you can help! Thanks in Advance!

 

[Debra Dalgleish]

You could check the pivot items, and if the matching item isn't found,
set the page field to "(All)", e.g.:

'===================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim pt2 As PivotTable
Dim pf2 As PivotField
Dim pi2 As PivotItem
Set pt = Sheets("Pivot").PivotTables("PivotTable1")
Set pt2 = Sheets("Pivot2").PivotTables("PivotTable1")
Set pf = pt.PivotFields("ARF")
Set pf2 = pt2.PivotFields("ARF")

If Target.Address = "$K$2" Then
For Each pi In pf.PivotItems
If pi = Target.Value Then
pf.CurrentPage = Target.Value
Exit For
End If
Next pi

For Each pi2 In pf2.PivotItems
If pi2 = Target.Value Then
pf2.CurrentPage = Target.Value
Exit For
End If
Next pi2

If pf.CurrentPage <> Target.Value Then
pf.CurrentPage = "(All)"
End If
If pf2.CurrentPage <> Target.Value Then
pf2.CurrentPage = "(All)"
End If

End If

End Sub
'===============================================