COPY BY MACRO DOWN TO LAST VALUE CELL IN COLUMN "A"

[K]

Hi all, I have macro set on a button (see below) in my Sheet2

Sub FORMULAS()
Dim ic As Long
With Sheets("SPLIT")
.Range("G2").FormulaR1C1 = "=RC[-6]&RC[-5]&RC[-4]"
.Range("H2").FormulaR1C1 = "=IF(RC[-1]=R[1]C[-1],"""",RC[-1])"
.Range("I2").FormulaR1C1 =
"=IF(RC[-1]="""","""",SUMIF(R2C7:R50000C7,RC[-1],R2C5:R50000C5))"
.Range("J2").FormulaR1C1 = "=ROUND(RC[-5],0)"
.Range("K2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("L2").FormulaR1C1 =
"=IF(RC[-4]="""","""",SUMIF(R2C7:R50000C7,RC[-4],R2C11:R50000C11))"
.Range("M2").FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-4]-RC[-1])"
.Range("N2").FormulaR1C1 =
"=IF(RC[-1]="""","""",SUM(RC[-3],RC[-1]))"
.Range("O2").FormulaR1C1 = "=IF(RC[-1]="""",RC[-4],RC[-1])"
.Range("P2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("Q2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("R2").FormulaR1C1 =
"=IF(RC[-10]="""","""",SUMIF(R2C7:R50000C7,RC[-10],R2C17:R50000C17))"
ic = Cells(Rows.Count, "A").End(xlUp).Row
.Range("G2:R2").Copy .Range("G2").Offset(0, 0).Range("A1:A" & ic -
1)
Application.CutCopyMode = False
End With

End Sub

I get error when i run the macro and this line (see below) get
highligted in module
..Range("G2:R2").Copy .Range("G2").Offset(0, 0).Range("A1:A" & ic - 1)
basically i am try to put formulas by macro in cells from G2 to R2 of
Sheet("SPLIT") and then i want macro to copy those formulas from
range G3 to R3 down to last value cell in column A. Please can
anybody tell that how can i correct my macro above or what am i doing
wrong? Thanks in advance for help

 

[Per Jessen]

Hi

Look at this

Sub FORMULAS()
Dim ic As Long
Dim LastRow As Integer
LastRow = Range("A65536").End(xlUp).Row

With Sheets("SPLIT")
.Range("G2").FormulaR1C1 = "=RC[-6]&RC[-5]&RC[-4]"
.Range("H2").FormulaR1C1 = "=IF(RC[-1]=R[1]C[-1],"""",RC[-1])"
.Range("I2").FormulaR1C1 =
"=IF(RC[-1]="""","""",SUMIF(R2C7:R50000C7,RC[-1],R2C5:R50000C5))"
.Range("J2").FormulaR1C1 = "=ROUND(RC[-5],0)"
.Range("K2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("L2").FormulaR1C1 =
"=IF(RC[-4]="""","""",SUMIF(R2C7:R50000C7,RC[-4],R2C11:R50000C11))"
.Range("M2").FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-4]-RC[-1])"
.Range("N2").FormulaR1C1 = "=IF(RC[-1]="""","""",SUM(RC[-3],RC[-1]))"
.Range("O2").FormulaR1C1 = "=IF(RC[-1]="""",RC[-4],RC[-1])"
.Range("P2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("Q2").FormulaR1C1 = "=ROUND(RC[-1],0)"
.Range("R2").FormulaR1C1 =
"=IF(RC[-10]="""","""",SUMIF(R2C7:R50000C7,RC[-10],R2C17:R50000C17))"
ic = Cells(Rows.Count, "A").End(xlUp).Row
.Range("G2:R2").Copy .Range("G3", .Cells(LastRow, "R"))
Application.CutCopyMode = False
End With

End Sub

Regards,
Per