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
Before
After
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