srinimtech
New Member
- Joined
- Mar 29, 2017
- Messages
- 3
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'
<tbody>
</tbody>
Calculations in 'Sheet2'
<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
<tbody>
</tbody>
THE PROBLEM:
The workbook would be like this, initially.
Input list in 'Sheet1'
<tbody>
</tbody>
'Sheet2'
<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:
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
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:
Code:
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