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

BenVan

New Member
Joined
Aug 22, 2012
Messages
3
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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:

Code:
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
 
Upvote 0
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:

Code:
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
 
Upvote 0
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:

Code:
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
 
Upvote 0
Hi,

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

Try:

Code:
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
 
Upvote 0
Try this one too. If B1 contains a negative number or text, no columns will be inserted.
Code:
Sub Insert_Columns()
    On Error Resume Next
    Columns("C").Resize(, Range("B1").Value).Insert
    On Error GoTo 0
End Sub
 
Upvote 0
Hi Holger, Ingolf and Peter,

Thank you very much for your help.

It actually works now --> Problem solved.

Thanks again!

BenVan
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top