Macro to copy Data

[howard]

I need code the will copy a formula into cell F9 and then copy this formula into all cells in column F where there is data in the last row in col E

I have listed my code below, which is not copying the data into F10 onwards

Sub Copy_Data ()

im LR As Long
LR = Cells(Rows.Count, "E").End(xlUp).Row
With Range("F9")
.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
.Range("f9").Copy
.Range("F10:F" & LR).Paste
.Application.CutCopyMode = False

End With
End Sub


Your assistance will be most appreciated

Regards

Howard

 

[Jon von der Heyden]

Hello Howard

You don't need to use copy since you are using that type of R1C1 formula, the following should achieve the same:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Copy_Data()<br><br><SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>LR = Cells(Rows.Count, "E").End(xlUp).Row<br><br>Range("F9:F" & LR).FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Can you confirm that you have data in column E below row 9?

 

[jindon]

This should do the same

Sub Copy_Data ()
 
Range("F9", Range("f" & Rows.Count).End(xlUp)).Formula = "=d9&"" ""&e9"
 
End Sub

 

[howard]

Hi Jon & Jindon

Thanks for the help

Regards

Howard