need to copy values not formulas

[wafs]

I created the following code to copy over some information, except that I just need the values copied and not the formula. I think I need to add .value, but not sure where it would go. any suggestions?

r = commentsstart
c = 0
c = c + 1
y = c + 103
TestRange6 = "A" + r
r = r + 1
Do While sheets(destsh).Range(TestRange6) <> 0
reportlocation = "A" + y
sheets(destsh).Range(TestRange6).Copy sheets(destsh).Range(reportlocation) ****thinking .value should go at the end of this line.
y = y + 1
reportlocation = "A" + y
Loop

 

[PHV]

Have a look at the PasteSpecial method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

It's not the COPY, it's the Paste part. You need Paste Special - VALUES.


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[wafs]

How can you use Paste Special in vba? I've not heard of it in vba.

 

[Bong]

Selection.PasteSpecial Paste:=xlPasteValues

_________________
Bob Rashkin

 

[SkipVought]

Simple....

Turn on your Macro Recorder and do it.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[wafs]

Thank you all for your help. I got my macro working correctly.

Wendy