Runtime error 1004

[ianb]

Hi I am sure this is a simple one for someone here.

Why is this erroring and what is the solution.
I was running just the pivottable2 and this was working fine.
Now I add another 2 and I gain an error.

Many thanks.

Sub Dailydate()

Sheets("Dashboard (Individual)").Select
  ActiveSheet.PivotTables("PivotTable2").PivotFields("Resolved Date"). _
  CurrentPage = Format(Now, "dd/mm/yyyy")
Application.Goto Range("a1")

Sheets("PDF Report").Select
  ActiveSheet.PivotTables("PivotTable7").PivotFields("Resolved Date"). _
  CurrentPage = Format(Now, "dd/mm/yyyy")
Application.Goto Range("a1")

Sheets("PDF Report").Select
  ActiveSheet.PivotTables("PivotTable8").PivotFields("Resolved Date"). _
  CurrentPage = Format(Now, "dd/mm/yyyy")
Application.Goto Range("a1")

End Sub

 

[NARAYANK991]

Hi Ian ,

The Excel help on this says :

Returns or sets the current page showing for the page field (valid only for page fields).

Can you confirm that the field Resolved Date is a page field in the pivot tables PivotTable7 and PivotTable8 ?

Narayan

 

[ianb]

Hi Narayan. Yes they are.

 

[ianb]

Confirmation :

Sheets("PDF Report").Select
  Range("AE113").Select
  ActiveSheet.PivotTables("PivotTable8").PivotFields("Resolved Date"). _
  ClearAllFilters
  ActiveSheet.PivotTables("PivotTable8").PivotFields("Resolved Date"). _
  CurrentPage = "15/07/2014"
"

 

[ianb]

Hi, they are using differnet connections.
I just tried one called Pivot Table 7 on the same connection and it has worked.
Do you know why it is not working for different connections.

 

[ianb]

I think I have solved it. The Pivot table I used on another sheet had a query built in (Pivot table 7 is a copy. yet when I have used an open pivot table (No MS Query) it has worked ok. must be conflicting MS Query and setting field. the other pivot table does have MS Query and yet I can set the date fine. hmmm. ok I have a solution yet do not know fully the cause..... thanks for the input. I think that is what prompted me to continue with my invesitgatio hence we found a solution.

 

[ianb]

Just an excel ninja's presence can lead to a solution.

 

[NARAYANK991]

Hi Ian ,

No , I think you found the solution yourself , and what led to it was your persistence. There is no greater inspiration for this , than Edison :

https://www.goodreads.com/author/quotes/3091287.Thomas_A_Edison

Narayan

 

[ianb]

Wonderful web site. I will read with great interest on Edison.

My solution did evolve. I have found that Setting pivot tables you can then set the filters for each of the pivot tables via VBA recording macro as some times just selecting filters does not show the data. if any one has found they are not showing any data then it is : 1. set pivot table / clear filters and add filters in a macro. hope this solution is good for someone else. many thanks.