Macro - Copy/Paste Problem

[BigNick1]

I'm using the below code to copy data between worksheets. All works well apart from the bold and italicised section which copies the formula across and not the calculated value. Any help will be great appreciated.

Sub CopyData()

Sheets("CustList").Select
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column CA
ThisValue = Cells([x], "CA").Value
If ThisValue <> 0 Then
Cells([x], "A").Copy
Sheets("FinalList").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
ActiveSheet.Paste
Sheets("CustList").Select

Cells([x], "B").Copy
Sheets("FinalList").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 2).Select
ActiveSheet.Paste
Sheets("CustList").Select

Cells([x], "C").Copy
Sheets("FinalList").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 3).Select
ActiveSheet.Paste
Sheets("CustList").Select

Cells([x], "D").Copy
Sheets("FinalList").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 4).Select
ActiveSheet.Paste
Sheets("CustList").Select

Cells([x], "E").Copy
Sheets("FinalList").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 5).Select
ActiveSheet.Paste
Sheets("CustList").Select

Cells([x], "F").Copy
Sheets("FinalList").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 6).Select
ActiveSheet.Paste
Sheets("CustList").Select

Cells([x], "G").Copy
Sheets("FinalList").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 7).Select
ActiveSheet.Paste
Sheets("CustList").Select

Cells([x], "CA").Copy
Sheets("FinalList").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 8).Select
ActiveSheet.Paste
Sheets("CustList").Select
End If
Next x
End Sub

 

[VoG]

Try

Cells([x], "CA").Copy
Sheets("FinalList").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(NextRow, 8).PasteSpecial Paste:=xlPasteValues

 

[sdsurzh]

Hi,

Instead of "ActiveSheet.Paste"

Try the below one

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Regards,
SK

 

[BigNick1]

Many thanks for this, it works perfectly. If you only knew how long I'd been playing with this.
Much appreciated,
Nick