Making dynamic data static

Hi there

Can anyone help please?

I want to take some data which is dynamic and make it static elsewhere in a workbook without doing a manual copy of the said data. Is this even possible?

For example, let's say Sheet1!A1 is the dynamic data I am using, and on Sheet2!A1 I want to replicate this data, I know I can just reference Sheet1!A1. However, this would make Sheet2!A1 dynamic, and I want it to be static. I know I can do this be just typing it in manually but I have a lot of data I would need to do this for, is there some way of making Sheet2!A2 static whilst still referencing Sheet1!A1?

Hope this makes sense.

Thank you

Hi clarkyblade,

To do what you want to do manually (in Excel 2003):
a. Select the cells you want to copy.
b. Edit > Copy (or CTRL-C)
c. Select the Top Left Destination Cell (in any workbook or worksheet)
d. Edit > Paste Special > Values > OK

Using VBA you would use something like the following (tested and working):

Please Login or Register  to view this content.

There is more efficient code, but this is the easiest to understand. If you wanted to change workbooks, you would need a Workbook reference in VBA. That is left as an exercise for the student.

Good luck.

Lewis

Brilliant, thank you very much.