*resolved* Pasting results into a dynamic range on a different worksheet

[TheRobster]

I have set up a spreadsheet in order to run some macro-based calculations, basically a simulation of a water network. It that has two worksheets. One is "Global Settings" where the user enters some basic settings and one called "Results" which records the outputs of the simulations. For various reasons I have had to create a variable which stores a dynamic range which changes every iteration of the simulation. This dynamic range then has a specific value placed in it (every cell) per iteration of the simulation. Code as below:

'create the range that will have the average flow values pasted in to it
Set paste_range = Range(Cells(row_number, 3), Cells(row_number + event_duration - 1, 3)) 

'paste the average_flow value in to the dynamic range
paste_range.Value = average_flow

Now the setting up of the dynamic range seems to work okay. The problem I am having is that it ALWAYS pastes the values into the dynamic range on to GLOBAL SETTINGS worksheet, where the simulation is run from, and I need them pasting in to the RESULTS worksheet. The reason I think it does this is that the code shown above is executed whilst the Global Settings worksheet is activated. For some reason I think the paste_range variable I created to hold the dynamic range becomes linked to the Global Settings worksheet.

I have tried creating the range so it is linked to the Results worksheet without success (I don't know the syntax to do this, or even if it's possible). Also tried using With Sheets ("Results") and tried getting it to paste the values that way but again it doesn't work and they always end up on the Global Settings worksheet.

Any ideas how I can get the values to paste in to the right worksheet?

Thanks
-Rob

Ps - I should add that I have also tried the following but it does not worK:

Set paste_range = Sheets("Results").Range(Cells(row_number, 3), Cells(row_number + event_duration - 1, 3))

I would have expected this to work but I get a "application-defined or object-defined error", which is strange because I thought this approach would work.

[westconn1]

I would have expected this to work but I get a "application-defined or object-defined error"

probably because the cells are in the active worksheet while the range is in the specified worksheet

try like

with Sheets("Results")
  Set paste_range = .Range(.Cells(row_number, 3), .Cells(row_number + event_duration - 1, 3))
end with

[TheRobster]

That's great, thanks westconn1. I take it the "." before the Range and Cells keywords are needed to make the code work with the With Sheets command?

[westconn1]

yes!