Run Code On One Sheet While In Another

I need to run code that updates one sheet while I'm in another sheet (ie. from a form sheet).

I have 3 sheets - called 'Main', 'Chart Data - From Pivot' and 'Chart Pivot Table'

I have code that takes certain data from 'Chart Pivot Table' and copy/pastes it into 'Chart Data - From Pivot' .

If I am in the 'Chart Pivot Table' it works but if I'm in any other sheet it works incorrectly.

How do I get it to run from the Main sheet correctly.

Here is the

Sub mcrCopyToChartData()

Dim Rng As Range, Dn As Range, nRng As Range, c As Integer
Dim ws As Worksheet

' I added this 'set ws line...' to perhaps point to the correct sheet but
' it did not seem to do anything different.

Set ws = ActiveWorkbook.Worksheets("Chart Pivot Table")

'this selects the current data from the 'chart pivot table'

Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).............