1

So am trying to tell VBA to insert a certain number of columns between two other columsn based on a cell reference.

I've managed to get excel to insert columns within my range:

Sub INSERT()

Range(Range("Data_FirstColumn").Offset(, 1)Range("Data_Net").Offset(, -2)).EntireColumn.INSERT

End Sub

Essentially I want to insert x number of columns between "Data_FirstColumn" and "Data_Net" based on a cell reference

EG: If the cell say 10, I want excel to insert 10 columns between "Data_FirstColumn" and "Data_Net". If the cell says 1, I want excel to insert 1 column between "Data_FirstColumn" and "Data_Net".

I've tried the following, but it keeps inserting columns to the left of "Data_FirstColumn", not between "Data_FirstColumn" and "Data_Net"

Sub INSERT()
Range(Range("Data_FirstColumn").Offset(, 1), Range("Data_Boundary").Offset(, -2)).Application.DisplayAlerts = False

Dim i As Integer

For i = 1 To Range("Assumptions!B26").Value
    Columns(1).INSERT
Next i

Application.DisplayAlerts = True

End Sub

Thanks!

1

It's just something simple like this:

Sub test()
Dim i As Integer

For i = 1 To Range("A1").Value

    Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Next i
End Sub

so for yours, using a named range, just throw in the shift:=xltoright and it will shift that column to the right, inserting the new one to the left of it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.