VBA insert n new columns and calculate difference of previous n-2 columns

Bering

Board Regular
Hello,

I have a workbook with 2 worksheets: in sheet1, column B, users enter a list of items (the number of item can vary but the last item is always called Total).
For each item in that list, a macro adds a corresponding column in sheet2 (headers = item) plus an additional one, unrelated to the list. The macro will also insert some formulae in each new column in sheet2 (row 6 to 53).

Example: in sheet2 there are 20 columns, the user enters 4 items in sheet1, the macro will create 5 additional columns, hence 25 in total.
For the last item in the list in sheet1, which is always called Total, I would like to add in the corresponding column in sheet2, a formula that calculates the difference of the n- 2 previous columns (3 in this example): item Total would correspond to Column 24 and I would like to populate the range row 6 to 53 with a formula: Column 21 - Column 22 - Column 23

I am not sure how I could achieve this.

Many thanks in advance

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

DanteAmor

Well-known Member
Assuming the items on sheet1 start in cell B2 and the headings on sheet2 are in row 5, try this:

VBA Code:
``````Sub insert_new_columns()
Dim f As Range, lc As Long, n As Long
Dim sh1 As Worksheet, sh2 As Worksheet

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")

Set f = sh1.Range("B:B").Find("Total", , xlValues, xlWhole, , , False)
If Not f Is Nothing Then
With sh1.Range("B2", f)
.Copy
n = .Rows.Count - 1
End With
If n > 0 Then
lc = sh2.Cells(5, Columns.Count).End(1).Column + 1
sh2.Cells(5, lc).PasteSpecial xlPasteValues, , , True
With sh2.Cells(6, lc + n)
If n = 1 Then
.Formula = "=" & .Offset(0, -n).Address(0, 0)
Else
.Formula = "=" & .Offset(0, -n).Address(0, 0) & "-SUM(" & .Offset(0, -n + 1).Address(0, 0) & ":" & .Offset(0, -1).Address(0, 0) & ")"
End If
.Copy sh2.Range(sh2.Cells(6, lc + n), sh2.Cells(53, lc + n))
End With
Else
MsgBox "No items"
End If
Else
MsgBox "The item 'Total' does not exist"
End If
End Sub``````

Bering

Board Regular
Thank you sooo much Dante, this works perfectly

Assuming the items on sheet1 start in cell B2 and the headings on sheet2 are in row 5, try this:

VBA Code:
``````Sub insert_new_columns()
Dim f As Range, lc As Long, n As Long
Dim sh1 As Worksheet, sh2 As Worksheet

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")

Set f = sh1.Range("B:B").Find("Total", , xlValues, xlWhole, , , False)
If Not f Is Nothing Then
With sh1.Range("B2", f)
.Copy
n = .Rows.Count - 1
End With
If n > 0 Then
lc = sh2.Cells(5, Columns.Count).End(1).Column + 1
sh2.Cells(5, lc).PasteSpecial xlPasteValues, , , True
With sh2.Cells(6, lc + n)
If n = 1 Then
.Formula = "=" & .Offset(0, -n).Address(0, 0)
Else
.Formula = "=" & .Offset(0, -n).Address(0, 0) & "-SUM(" & .Offset(0, -n + 1).Address(0, 0) & ":" & .Offset(0, -1).Address(0, 0) & ")"
End If
.Copy sh2.Range(sh2.Cells(6, lc + n), sh2.Cells(53, lc + n))
End With
Else
MsgBox "No items"
End If
Else
MsgBox "The item 'Total' does not exist"
End If
End Sub``````

Last edited by a moderator:

Bering

Board Regular
Hello Dante,

Just a question if you don't mind: suppose I need to insert the formula =P*\$E in range 5 - last row in column P; How would that translate using the notation Offset().Address() in your code above?

Many thanks

DanteAmor

Well-known Member

P*E in P would produce a circular reference error.
It would always be P*E, because it may be that P is empty. I don't quite understand what it would be like, since the columns are dynamic, it depends on the number of items, so P could be empty or with an item or with "Total"

Bering

Board Regular
Apologies, I meant to say P\$4*E (I have an amount in row P4 that I need to allocate by a ratio in column E from row 5 to last row)

P*E in P would produce a circular reference error.
It would always be P*E, because it may be that P is empty. I don't quite understand what it would be like, since the columns are dynamic, it depends on the number of items, so P could be empty or with an item or with "Total"

DanteAmor

Well-known Member

Sorry, but I still don't understand. This is very clear to you because you are seeing it and you know your data.
It would help if you put an image explaining where you want the formula.

Bering

Board Regular

I hope this clarifies, thank you

DanteAmor

Well-known Member
Always in column P? Or in the first column where the macro begins to put items?
Only in the P or also in the Q and in the R?

Bering

Board Regular
all the columns created by the macro actually, so yes P, Q and R in this example.

Many thanks

Always in column P? Or in the first column where the macro begins to put items?
Only in the P or also in the Q and in the R?

Replies
10
Views
104
Replies
6
Views
69
Replies
6
Views
61
Replies
1
Views
26
Replies
8
Views
249