Copy formulas from row above after adding rows - both actions using VBA

[srinimtech]

Hei guys,

I need help in copying formulas from one row to rows below it using VBA.

The text in blue color shall be considered as row and column headers of a worksheet.

Input list in 'Sheet1'

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row no[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item name[/TD]
[TD]Unit rate[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item 1[/TD]
[TD]10[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item 2[/TD]
[TD]5[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Item 3[/TD]
[TD]8[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Item 4[/TD]
[TD]9[/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]



Calculations in 'Sheet2'


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row no[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item name[/TD]
[TD]Unit rate[/TD]
[TD]Quantity[/TD]
[TD]Basic price[/TD]
[TD]Taxes[/TD]
[TD]Total price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item 1[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]150[/TD]
[TD]15[/TD]
[TD]165[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item 2[/TD]
[TD]5[/TD]
[TD]16[/TD]
[TD]80[/TD]
[TD]8[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Item 3[/TD]
[TD]8[/TD]
[TD]13[/TD]
[TD]104[/TD]
[TD]10.4[/TD]
[TD]114.4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Item 4[/TD]
[TD]9[/TD]
[TD]16[/TD]
[TD]144[/TD]
[TD]14.4[/TD]
[TD]158.4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]478[/TD]
[TD]47.8[/TD]
[TD]525.8[/TD]
[/TR]
</tbody>[/TABLE]

The columns A, B, C in 'Sheet2' are copied from 'Sheet1'.
The values in columns D, E & F are calculated within 'Sheet2'
So, if the formulas are shown in the above table, they would be



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row no[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item name[/TD]
[TD]Unit rate[/TD]
[TD]Quantity[/TD]
[TD]Basic price[/TD]
[TD]Taxes[/TD]
[TD]Total price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=Sheet1!A2[/TD]
[TD]=Sheet1!B2[/TD]
[TD]=Sheet1!C2[/TD]
[TD]=B2*C2[/TD]
[TD]=D2*10%[/TD]
[TD]=D2+E2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=Sheet1!A3[/TD]
[TD]=Sheet1!B3[/TD]
[TD]=Sheet1!C3[/TD]
[TD]=B3*C3[/TD]
[TD]=D3*10%[/TD]
[TD]=D3+E3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]=Sheet1!A4[/TD]
[TD]=Sheet1!B4[/TD]
[TD]=Sheet1!C4[/TD]
[TD]=B4*C4[/TD]
[TD]=D4*10%[/TD]
[TD]=D4+E4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=Sheet1!A5[/TD]
[TD]=Sheet1!B5[/TD]
[TD]=Sheet1!C5[/TD]
[TD]=B5*C5[/TD]
[TD]=D5*10%[/TD]
[TD]=D5+E5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]=Sum(D2:D5)[/TD]
[TD]=Sum(E2:E5)[/TD]
[TD]=Sum(F2:F5)[/TD]
[/TR]
</tbody>[/TABLE]

THE PROBLEM:

The workbook would be like this, initially.


Input list in 'Sheet1'

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row no[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item name[/TD]
[TD]Unit rate[/TD]
[TD]Quantity[/TD]
[/TR]
</tbody>[/TABLE]


'Sheet2'


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row no[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item name[/TD]
[TD]Unit rate[/TD]
[TD]Quantity[/TD]
[TD]Basic price[/TD]
[TD]Taxes[/TD]
[TD]Total price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=Sheet1!A2[/TD]
[TD]=Sheet1!B2[/TD]
[TD]=Sheet1!C2[/TD]
[TD]=B2*C2[/TD]
[TD]=D2*10%[/TD]
[TD]=D2+E2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]=SUM(INDIRECT("D2:D"&ROW()-1))[/TD]
[TD]=SUM(INDIRECT("E2:E"&ROW()-1))[/TD]
[TD]=SUM(INDIRECT("F2:F"&ROW()-1))[/TD]
[/TR]
</tbody>[/TABLE]


Once I run the macro,
1. it should add rows between row 2 & 3 in 'Sheet2'. The number of rows would be equal to number of items in 'Sheet1' minus 1
2. Copy formulas from row 2 to end of the list.
3. The formula for 'Total' updates itself to calculate total up to the row above.

The code I used to add rows is as below:

Sub AddRows()

    Dim num_rows As Long


    Set s1 = Worksheets("Sheet3")

    Sheets("Sheet2").Select

    num_rows = s1.Cells(12, 5)

    ActiveSheet.Rows(10 & ":" & num_rows + 8).Insert Shift:=xlDown
    
End Sub

In the code above, the number of rows to be added is calculated in 'Sheet3'.

I need help in copying formulas from row 2 to other rows.

PS: The code given above to add rows was taken from some post in this form and modified to me requirement.

Regards,

Srini

 

[AlphaFrog]

Make your table on Sheet2 a listobject type Table
Creating an Excel Table

This will add rows to that table above the last row. The formulas will autofill.

[color=darkblue]Sub[/color] AddRows()
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    [color=darkblue]With[/color] Sheets("Sheet2").ListObjects(1)
        [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] LastRow - .Range.Rows.Count + 1
            .ListRows.Add .Range.Rows.Count - 1
        [color=darkblue]Next[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]