1

I need this code to be rewritten to calculate without selecting the "Random_Cal" sheet.

Sheets("Random_Cal").PivotTables("PivotTable3").PivotCache.Refresh
Sheets("Random_Cal").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields("3a").PivotFilters.Add Type _
    :=xlValueIsBetween, DataField:=ActiveSheet.PivotTables("PivotTable3"). _
    PivotFields("3b"), Value1:=1, Value2:=Range("AI52")

The code all works but the button that activates the code is on a different sheet to "Random_Cal". Currently when the button is pressed the screen jumps to "Random_Cal" then jumps back to the starting sheet. I just need this section of code to be altered to stay on the first sheet but to calculate the "Random_Cal" sheet. Thanks

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Brandon
  • 11
  • 2
  • I have tried this {Sheets("Random_Cal").PivotTables("PivotTable3").PivotCache.Refresh Sheets("Random_Cal").PivotTables("PivotTable3").PivotFields("3a").PivotFilters.Add Type _ :=xlValueIsBetween, DataField:=Sheets("Random_Cal").PivotTables("PivotTable3"). _ PivotFields("3b"), Value1:=1, Value2:=Range("AI52")} But this did not work and i just received the Error 400 code. – Brandon Jul 30 '18 at 10:20

1 Answers1

3

Use a With ... End With block that passes the parent worksheet reference on.

with workSheets("Random_Cal")
    .PivotTables("PivotTable3").PivotCache.Refresh
    .PivotTables("PivotTable3").PivotFields("3a").PivotFilters.Add _
       Type:=xlValueIsBetween, DataField:=.PivotTables("PivotTable3"). _
       PivotFields("3b"), Value1:=1, Value2:=.Range("AI52")
end with

How to avoid using Select in Excel VBA