VBA Query

[mike]

Hi Guys,

How would I programmatically copying the contents of
sheet1 (with formula's) and only paste the values onto
sheet 2.

I recorded the code and it works, but it crashes when you
try to run macro twice. The user has the option of
changing contents of sheet 1 as much as they want until
they are happy with result. Do I need to create a separate
sheet each time I run the macro?

Thanks,

Mike

 

[steve]

Mike,

Try this
Sheets("sheet1").Cells().Copy
Sheets("sheet2").Cells(1, 1).PasteSpecial Paste:=xlValues

be aware that when you go to sheet2, all cells will be highlighted.
You may want to add code to change that.

Also you could modify your code to only copy a defined range of sheet1.
There are many ways to do this. Post back if you want help with this
(include information about how the range is defined).

And when pasting - you only need to set the first cell. Excel will do the
rest.

 

[Tom Ogilvy]

One other way

for a specified range
Worksheets("Sheet2").Range("A1:B200").Value = _
Worksheets("Sheet2").Range("A1:B200").Value

or for all the used area of the sheet
set rng = Worksheets("Sheet1").UsedRange
worksheets("Sheet2").Range(rng.address).Value = _
rng