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'

Row no	Column A	Column B	Column C
1	Item name	Unit rate	Quantity
2	Item 1	10	15
3	Item 2	5	16
4	Item 3	8	13
5	Item 4	9	16

<tbody>
</tbody>

Calculations in 'Sheet2'

Row no	Column A	Column B	Column C	Column D	Column E	Column F
1	Item name	Unit rate	Quantity	Basic price	Taxes	Total price
2	Item 1	10	15	150	15	165
3	Item 2	5	16	80	8	88
4	Item 3	8	13	104	10.4	114.4
5	Item 4	9	16	144	14.4	158.4
6			Total	478	47.8	525.8

<tbody>
</tbody>

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

Row no	Column A	Column B	Column C	Column D	Column E	Column F
1	Item name	Unit rate	Quantity	Basic price	Taxes	Total price
2	=Sheet1!A2	=Sheet1!B2	=Sheet1!C2	=B2*C2	=D2*10%	=D2+E2
3	=Sheet1!A3	=Sheet1!B3	=Sheet1!C3	=B3*C3	=D3*10%	=D3+E3
4	=Sheet1!A4	=Sheet1!B4	=Sheet1!C4	=B4*C4	=D4*10%	=D4+E4
5	=Sheet1!A5	=Sheet1!B5	=Sheet1!C5	=B5*C5	=D5*10%	=D5+E5
6			Total	=Sum(D2:D5)	=Sum(E2:E5)	=Sum(F2:F5)

<tbody>
</tbody>

THE PROBLEM:

The workbook would be like this, initially.


Input list in 'Sheet1'

Row no	Column A	Column B	Column C
1	Item name	Unit rate	Quantity

<tbody>
</tbody>

'Sheet2'

Row no	Column A	Column B	Column C	Column D	Column E	Column F
1	Item name	Unit rate	Quantity	Basic price	Taxes	Total price
2	=Sheet1!A2	=Sheet1!B2	=Sheet1!C2	=B2*C2	=D2*10%	=D2+E2
3			Total	=SUM(INDIRECT("D2:D"&ROW()-1))	=SUM(INDIRECT("E2:E"&ROW()-1))	=SUM(INDIRECT("F2:F"&ROW()-1))

<tbody>
</tbody>

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]