Inserting a new column in another sheet

[iterature]

Hi.
I have some code which runs by clicking a button in Sheet1.
I want the code to insert a new column in Sheet2 (using a variable 'colNo', which holds the column insert location as a number.)

I thought the following piece of code would do this okay:

Dim colNo As Long
colNo = 2

Sheets("Sheet2").Select
Columns(colNo).Select
Selection.Insert Shift:=xlToRight

. . . but it doesn't work.
I keep getting a '1004' error(?)

Does anyone know how to do this correctly?

Thanks in advance for your help.

[VBA Noob]

No need to select.

Try

 
Dim colNo As Long
colNo = 2

    With Sheets("Sheet2").Columns(colNo)
        .Insert Shift:=xlToRight
    End With

VBA Noob

[echiesse]

Hi iterature,

I copy/pasted your original code and it worked here.

You might be having problems in another part of your code.

Eric.

[iterature]

'VBA Noob' - that seems to work fine. Many thanks.

echiesse - the original code doesn't even work on my system when it's the only code in the button.


Thanks for your help, guys.

[VBA Noob]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save