Excel VBA error Ambiguous Name detected: Worksheet_Change

[Maggy0911]

I just want my 3 pivot tables to automatically refresh. I'm not sure why I keep getting this. I made sure that the data sheet is named correctly as well as all three pivot tables. Can someone help please?

Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("Data").PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Data").PivotTables("PivotTable2").PivotCache.Refresh
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Data").PivotTables("PivotTable3").PivotCache.Refresh
End Sub

 

[Rick Rothstein]

I just want my 3 pivot tables to automatically refresh. I'm not sure why I keep getting this. I made sure that the data sheet is named correctly as well as all three pivot tables. Can someone help please?

Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("Data").PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Data").PivotTables("PivotTable2").PivotCache.Refresh
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Data").PivotTables("PivotTable3").PivotCache.Refresh
End Sub

You can only have one procedure with any given name in a module... you have the Worksheet_Change event procedure name using three times... that is why you get the Ambiguous Name error... the compiler does not know which one to use. Put all of your code into a single procedure like this...

Private Sub Worksheet_Change(ByVal Target As Range)
  Worksheets("Data").PivotTables("PivotTable1").PivotCache.Refresh
  Worksheets("Data").PivotTables("PivotTable2").PivotCache.Refresh
  Worksheets("Data").PivotTables("PivotTable3").PivotCache.Refresh
End Sub

 

[Maggy0911]

Thank you Rick! That worked for that error but now I am getting an run-time error 1004 Unable to get the PivotTables properties of the worksheet class.

You can only have one procedure with any given name in a module... you have the Worksheet_Change event procedure name using three times... that is why you get the Ambiguous Name error... the compiler does not know which one to use. Put all of your code into a single procedure like this...

Private Sub Worksheet_Change(ByVal Target As Range)
  Worksheets("Data").PivotTables("PivotTable1").PivotCache.Refresh
  Worksheets("Data").PivotTables("PivotTable2").PivotCache.Refresh
  Worksheets("Data").PivotTables("PivotTable3").PivotCache.Refresh
End Sub

 

[Rick Rothstein]

Thank you Rick! That worked for that error but now I am getting an run-time error 1004 Unable to get the PivotTables properties of the worksheet class.

Unfortunately, I am unable to help you with that part of your question as I have no experience using Pivot Tables. Since this is a different question from the one that started this thread, you may want to start a new thread for this particular problem... that way you will expose your new question to the entire volunteer group instead of only those who viewed your original question.