VBA - Inserting a variable number of columns based on the value of a cell

[BenVan]

Hi all,

It is the very first time I work with VBA and I am struggling quite a bit.

I have an Excel sheet in wich I have to insert a number of columns equal to a value in a cell. Note that the value in this cell will change over time. As a result, it is always the same cell that I would like to refer to. I have created a simple macro that inserts one single column in order to see the basic code. Unfortunately, I am unable to adjust it to arrive at the desired result.

Can anyone help me out?

Thanks
_________________

Sub Macro1()
'
' Macro1 Macro
'
'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

 

[patel45]

can you attach a link to a samplefile with before and after situation ?

 

[HaHoBe]

Hi, BenVan,

we´re talking about action on the very same sheet? Here B1 is the cell to deliver the numbers of columns to insert (must be greater than 0). Code goes into a Standard module:

If Range("b1").Value <= 0 Then Exit Sub
With Range(Cells(1, 3), Cells(1, 2 + Range("B1"))).EntireColumn
  .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With

Ciao
Holger

 

[BenVan]

Hi Holger,</SPAN>

Thank you very much for your answer. I am indeed talking about a reference cell in the same sheet.</SPAN>

I have tried to run the query that you submitted, but the following message pops up: </SPAN>

“Compile Error: </SPAN>

Expected: expression”</SPAN>

Am I doing something wrong?
</SPAN>
Also when I add "Sub Name ()" on top and "End Sub" at the end, it doesn't work.

Thanks again for your help.

Hi, BenVan,

we´re talking about action on the very same sheet? Here B1 is the cell to deliver the numbers of columns to insert (must be greater than 0). Code goes into a Standard module:

If Range("b1").Value <= 0 Then Exit Sub
With Range(Cells(1, 3), Cells(1, 2 + Range("B1"))).EntireColumn
  .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With

Ciao
Holger

 

[HaHoBe]

Hi, BenVan,

if you just copied the code into a standard module the colour for this should turn red (by default) and release the note you mentioned.

What version of Excel do you use?= If I use the code with Excel2000 xlFormatFromLeftOrAbove throws up an error - I think this parameter was introduced either with 2002/XP or 2003.

If you don´t want the formats to be copied as well please try:

Public Sub BenVan_120823()
If Range("b1").Value <= 0 Then Exit Sub
With Range(Cells(1, 3), Cells(1, 2 + Range("B1"))).EntireColumn
  .Insert Shift:=xlToRight ', CopyOrigin:=xlFormatFromLeftOrAbove
End With
End Sub

This codes needs to be started either from the VBE or from the Macro Window (ALT+F8) if the module hasn´t been declared private and as long as the code is in a standard module.

Ciao,
Holger

 

[Ingolf]

Hi,

For me HaHoBe code work fine. You have to put a number in B1.

Try:

Sub InsertColumns
If Range("b1").Value <= 0 Then Exit Sub
With Range(Cells(1, 3), Cells(1, 2 + Range("B1"))).EntireColumn
  .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With
End Sub

 

[Peter_SSs]

Try this one too. If B1 contains a negative number or text, no columns will be inserted.

Sub Insert_Columns()
    On Error Resume Next
    Columns("C").Resize(, Range("B1").Value).Insert
    On Error GoTo 0
End Sub

 

[BenVan]

Hi Holger, Ingolf and Peter,

Thank you very much for your help.

It actually works now --> Problem solved.

Thanks again!

BenVan