CODEWITHSUNDEEP
excelvba
Brandon
Brandon

Reputation: 11

Prevent VBA from selecting different sheet

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

Upvotes: 1

Views: 52

Answers (1)

user4039065
user4039065

Reputation:

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

Upvotes: 3

Related Questions