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

[Bering]

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

 

[DanteAmor]

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

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]

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:

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]

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]

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]

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]

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]

I hope this clarifies, thank you

 

[DanteAmor]

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]

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?