0

strange one this

I have a pivot table connected to a data source via power pivot / OLAP. I have created a vba button to add a field to the row section of the pivot however when I run...

With ActiveSheet.PivotTables("PivotTable1")
 .AddDataField ActiveSheet.PivotTables( _
 "PivotTable1").CubeFields("[Measures].[Sum of Unique_Customers]"), "Unique Customers"
End With

I get the object error 1004 runtime error 1004 application-defined or object-defined error.

However if I refresh the pivot / data connection then it works fine. I have put in place a workaround to refresh the pivot each time the workbook opens but I fear as my data grows this will become resource heavy. Does anyone know of a solution or a reason to why the error occurs? It seems vb does not recognise the field until the data is refreshed. For info the field is a raw field and not a MDX calc or anything

Many thanks!

Community
  • 1
  • 1
Baseline9
  • 101
  • 1
  • 3
  • 12
  • I would avoid using "ActiveSheet" at all costs. That could be causing a scope issue with the pivot table. Also, have you verified the PivotTable's name? – StormsEdge Apr 19 '16 at 12:20
  • Brill thanks for this - I'm looking to replace "ActiveSheet" in my code. Do you mean give the pivot table an proper name rather than "PivotTable!"? I will try this as well :) – Baseline9 Apr 27 '16 at 12:02
  • I tired to remove the active sheet and rename my pivot but I still got the error :( Sheets("Summary").PivotTables("StatsSummary") – Baseline9 Apr 27 '16 at 12:36

1 Answers1

0

Hi managed to find a solution it was simple in the end - I had "On Error Resume Next" in parts of my code (from an old version) I took these out and its now working :)

Baseline9
  • 101
  • 1
  • 3
  • 12