Run Macro from other than Activesheet

[tlc_in_OK]

I can't believe I couldn't find a solution to this on the board...
I created a macro that manipulates data on a worksheet with a pivot table. Now the users want to hide that particular worksheet so it can't be altered (it feeds a graph, so that's the worksheet they want to be "active"). Below is the code I have that works fine, but I can't seem to make the adjustments to make it work when the active sheet is "Status_Graph". I've tried specifying Worksheet("Pivot")., but it errors with the pivot table references. I need to adjust all the references to "Activesheet" so this will run when another worksheet is active. Can someone assist?

Sub CopyPivotInfoTesting()
Dim RngPS As Range, RngPCR As Range, Rng As Range
With Sheets("Pivot").PivotTables("PivotTable1")
Columns("A:A").Select
Selection.NumberFormat = "0%"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Offer_to_Target_Pct"). _
AutoSort xlDescending, "Offer_to_Target_Pct"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Offer_to_Target_Pct"). _
PivotItems("(blank)").Position = ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("Offer_to_Target_Pct").PivotItems.Count
End With
 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_Status")
.PivotItems("Accepted").Visible = True
.PivotItems("No Bid-No Sale").Visible = False
.PivotItems("Rejected").Visible = False
.PivotItems("Pending").Visible = False
 
With Sheets("Pivot")
Set RngPS = .Range(.Range("A6"), .Range("C" & Rows.Count).End(xlUp))
Set Rng = .Range(.Range("F$2"), .Range("H" & Rows.Count).End(xlUp))
Rng.Resize(, 8).ClearContents
RngPS.Copy .Range("F2")
End With
 
End With
End Sub

 

[BMRC]

Try to replace

ActiveSheet.PivotTables("PivotTable1").PivotFields("

with

Worksheets("PIVOT").PivotTables("PivotTable1").PivotFields("

 

[Hermanito]

What are the tabnames of your worksheets? And what are the codenames? In your VBE window, on the left, for each worksheet you see 2 names, first one is the codename, second one (between parentheses) is the tabname.

The Status_Graph sheet, is that actually a worksheet, or is it a graph sheet (containing only one single graph)?

 

[tlc_in_OK]

BMRC- I tried that before but I get a runtime error (Unable to get the Pivot Tables property of the Worksheet Class) at this point in the code:

    Worksheets("Pivot").PivotTables("PivotTable1").PivotFields("Offer_to_Target_Pct"). _
        PivotItems("(blank)").Position = ActiveSheet.PivotTables("PivotTable1"). _
        PivotFields("Offer_to_Target_Pct").PivotItems.Count

Hermanito- I have three worksheets: Sheet1(Status_Graph), Sheet2(Pivot), Sheet3(All_Data)

Status_Graph happens to be a worksheet containing only one singel graph.

 

[Hashiru]

Hi

Sorry I cannot go through your code to identify the line of code that hide your worksheet. But here is a code that hide a sheet.

Assumption

The worksheet that you want to hide is called TryToHideMe

Sheets("TryToHideMe").Select
ActiveWindow.SelectedSheets.Visible = False

Courtesy
Hope this helps

NB: Place this in a seperate module

 

[tlc_in_OK]

I'm not trying to hide the worksheet, I'm trying to alter a macro that was developed using Activesheet, to run once that sheet is hidden and can't possibly be the active sheet.

 

[BMRC]

BMRC- I tried that before but I get a runtime error (Unable to get the Pivot Tables property of the Worksheet Class) at this point in the code:

    Worksheets("Pivot").PivotTables("PivotTable1").PivotFields("Offer_to_Target_Pct"). _
        PivotItems("(blank)").Position = ActiveSheet.PivotTables("PivotTable1"). _
        PivotFields("Offer_to_Target_Pct").PivotItems.Count

Hermanito- I have three worksheets: Sheet1(Status_Graph), Sheet2(Pivot), Sheet3(All_Data)

Status_Graph happens to be a worksheet containing only one singel graph.

There is still an "ActiveSheet." crawling around in there...

 

[tlc_in_OK]

Thank you, that was it. . .I missed one! Obviously, I've been staring at this too long.

One thing though, and maybe you can assist. . .at the beginning, where it selects column "A" and sets the format, it's now doing that in column "A" of whichever sheet I run it from (i.e. the Activesheet). The rest of it sorts and filters the pivot table, and copies/pastes just fine on the "Pivot" worksheet. Something else must need to go in there. . .any ideas?

With Worksheets("Pivot").PivotTables("PivotTable1")
    Columns("A:A").Select
    Selection.NumberFormat = "0%"

 

[BMRC]

Try this out and see if it works:

With Worksheets("Pivot").PivotTables("PivotTable1")
       Range("Pivot!A:A").NumberFormat = "0%"

 

[tlc_in_OK]

That did it. . .thanks for the assist!