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

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

Hi therobsteruk

Try this

Please Login or Register  to view this content.

That's great, thanks jaslake. Can't tell you how many hours I spent trying to get that to work.

Can you explain why it's necessary to put the full stops in? I would have thought you could just do:

Please Login or Register  to view this content.

within the With Sheets statement, i.e. just reference the whole line rather than having to qualify the Range and Cells keywords with fullstops.

Also was my suspicion correct? When you Set Range on an active worksheet does that range then become associated with that worksheet only?

Thanks
-Rob

Hi Rob

I'm certainly no expert but the "Full Stops" as you call them (is that truly what they're called?...I call them Dots) tie the range to the With Statement Worksheet. Without the Dots the range reference is to the Active Worksheet. With the Dots the range reference is to the With Worksheet.

The same logic applies to this

was my suspicion correct? When you Set Range on an active worksheet does that range then become associated with that worksheet only

PS: it appears to me you may have marked your Thread as SOLVED incorrectly...
Try this
To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save