Paste Special Values

[husky101]

Hi NG,
I have this code, which is used to copy a column containing a formula,
and pastes into another sheet.

However, it pastes the formula, but I need it to paste the values, and
I don't know how to modify this code to do it.

This is a code I found on this forum some time ago and it has been very
useful to me.

any help will be greatly appreciated.

Cheers Paul

Sub TotalPriceCopy()

Application.ScreenUpdating = False
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Set wks1 = Workbooks("ClearSaleUpload
Master.xls").Worksheets("IMPUT")
Set wks2 = Workbooks("ClearSaleUpload
Master.xls").Worksheets("ClearSaleUpload")

With wks1
.Range(.Range("G2"), .Range("G2").End(xlDown)).Copy
wks2.Range("G2")
End With

Set wks1 = Nothing
Set wks2 = Nothing
Application.ScreenUpdating = True
End Sub

 

[Norman Jones]

Hi Husky,

Try Changing the followin portion of your code:

With wks1
.Range(.Range("G2"), .Range("G2").End(xlDown)).Copy
wks2.Range("G2")
End With

to:

With wks1
.Range(.Range("G2"), .Range("G2").End(xlDown)).Copy

wks2.Range("G2").PasteSpecial (xlValues)
End With
Application.CutCopyMode = False

 

[husky101]

Hi Norman,
Thanks for the assist, however it comes up with an error
Unable to get the PasteSpecial property of the range class

I should have mentioned I'm using XP with Excel 2003.

thanks again.

 

[Norman Jones]

Hi Husky,

The code works for me.

Providing that I put:

.Range(.Range("G2"), .Range("G2").End(xlDown)).Copy

wks2.Range("G2").PasteSpecial (xlValues

wks2.Range("G2")

Where this should be a single line of code.

 

[husky101]

Hi Norman,
Sorry for not getting back sooner, but that works great.

Thanks for this.

Cheers