Copying a value from a formula, from one sheet to another

[Gwhaou]

Hello I tried to use this code to copy all the data from the column P6:P (last line) from the sheet "Original" and paste it on another sheet called "Test", well actually those data from the sheet "Original" are values, which are sums values by using FormulaR1C1.

When I copy that on the sheet called "Test" from the Cell "H2", got error called #REF

This is the code :

Sub Copy_Paste()

Dim lg As Long
lg = Sheets("Original").Range("A" & Rows.Count).End(xlUp).Row ' used to know the last row 

Sheets("Original").Range("P6:P" & lg).Copy Sheets("Test").Range("H2")

End Sub

This is the code used to make sums on the initial Sheet :

Sheets("Original").Range("P6").FormulaR1C1 = "=RC[-8]+RC[-7]+RC[-6]"
Sheets("Original").Range("P6").AutoFill Destination:=Sheets("Original").Range("P6:P" & lg)

A little help would be appreciated

 

[Fluff]

Do you just want to copy the values?

 

[Gwhaou]

Do you just want to copy the values?

Yes actually I want the sums values instead of that the code transfered the formula itself

 

[Fluff]

In that case you can use

Sheets("Test").Range("H2").Resize(lg - 5).Value = Sheets("Original").Range("P6:P" & lg).Value

 

[Gwhaou]

In that case you can use

Sheets("Test").Range("H2").Resize(lg - 5).Value = Sheets("Original").Range("P6:P" & lg).Value

Perfect it's working

 

[Fluff]

You're welcome & thanks for the feedback.