Copy/Paste Special on Worksheet Deactivation

[TheRobsterUK]

I have a worksheet that requries the user to input some data, then they need to select a different worksheet in order to carry out some calculations. I need a macro that copies/pastes values from the user input range to a different section of the user-input worksheet whenever the user selects a different worksheet.

Here's some code that I thought would work (code is located in the worksheet module):

Please Login or Register  to view this content.

It almost works but the macro repeats itself a large number of times, and also sometimes forces Excel to terminate. I can't understand why it does this though. Can anyone provide some advice? Essentially what I want is some code that copies/pastes a set of values from one range to another in the currently active worksheet before another worksheet is activated.

I put the HasRun variable in to try and get the routine to run only once but it doesn't really seem to have helped (still repeats many times...)

Thanks
Rob

Ps - I am using Excel 2007.

[TheRobsterUK]

Simply using a Range=Range approach works without any problems though, e.g. on the active worksheet I am navigating away from, this embedded code works fine:

Please Login or Register  to view this content.

I.e. it makes cell J9 equal to cell I9. Wonder if the original problem has something to do with the copy/paste functions then?

*edit*

This is also works but copies the cells verbatim (including formatting) which isn't what I want, I just want to copy/paste the values:

Please Login or Register  to view this content.

Seems to be some problem with the pastespecial function then and it makes it just repeat the command over and over many times....?

Thanks again
Rob

[watersev]

pastespecial method can not force the code to restart in deactivation event..you'd better posted your workbook with abnormal code functioning

[TheRobsterUK]

Right, got it sorted. Can't believe how simple it was in the end.

Please Login or Register  to view this content.

[pike]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save