Search code examples
excelvba

Adding multiple columns all at once

I want to insert four columns to the left of the first column and two columns to the right of another particular column all at once with specific headers.

The first column having no header, the second one will be Portfolio Identifier, the third Report Date (MM/DD/YYYY) then lastly Security Identifier Type("CUSIP","SEDOL").

I get an error

subscript out of range

Simultaneously, I need to add two columns with no headers to the right of a particular column.

Private Sub CommandButton3_Click()

With Sheets("File Source").Columns(1).Resize(, 4)
    .Insert
    .Offset(, -4).Rows(1).Value = Array("", "Portfolio Identifier", "Report Date (MM/DD/YYYY)", "Security Identifier Type("CUSIP","SEDOL")")
End With

End Sub
Solution

  • Insert Columns With Headers

    Before

    enter image description here

    After

    enter image description here

    The Calling Procedure

    Private Sub CommandButton3_Click()
    
    Dim AfterTitles As Variant: AfterTitles = Array("Part1", "Part2")
    Dim BeforeTitles As Variant: BeforeTitles = Array( _
        "", "Portfolio Identifier", "Report Date (MM/DD/YYYY)", _
        "Security Identifier Type(""CUSIP"",""SEDOL"")")
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("File Source")
    
    InsertIndexedWithTitles ws, 6, AfterTitles, True, True, True
    InsertIndexedWithTitles ws, 1, BeforeTitles, False, True, True
    
End Sub

    The Helper Procedure (Method)

    Sub InsertIndexedWithTitles( _
        ws As Worksheet, _
        ColumnIndex As Long, _
        ColumnTitles As Variant, _
        Optional InsertAfter As Boolean = False, _
        Optional BoldHeaders As Boolean = False, _
        Optional AutoFitColumns As Boolean = False)
    Dim cCount As Long: cCount = UBound(ColumnTitles) - LBound(ColumnTitles) + 1
    With ws.Cells(ColumnIndex)
        .Offset(, Abs(InsertAfter)).EntireColumn.Resize(, cCount).Insert
        With .Offset(, IIf(InsertAfter, 1, -cCount)).Resize(, cCount)
            .Value = ColumnTitles
            If BoldHeaders Then .Font.Bold = True
            If AutoFitColumns Then .EntireColumn.AutoFit
        End With
    End With
End Sub