Cannot Insert Column VBA

**atardif** · 07-21-2011, 06:44 PM

Dear All

I would greatly appreciate your help on this, because I am going crazy.

I have a spreadsheet filled with data (no merged cells) and I created a macro to sort the spreadsheet. Everything was working perfectly, until I tried to insert a column using code at the beginning of the macro.

The code is

     Columns("A:A").Select
    Selection.Insert Shift:=xlToRight

When the macro now runs, the first row acts as if a column has been inserted, i.e it shifts to the right, but the remainder of the spreadsheet does not.

I've tried shifting data to a new sheet, creating a new module, inserting another column, nothing works!!! However, if I step-into the macro, after stepping over the code once (where it does as above), when I shift the cursor above the code and step over it again, it inserts the column!

This is driving me to distraction, any thoughts or help would be greatly appreciated!!

Thanks
Alex

**tigertiger** · 07-21-2011, 06:58 PM

try the alternative

 Columns("A:A").Insert shift:=xltotright

or

 ActiveSheet.Columns("A:A").Insert shift:=xltotright

**atardif** · 07-23-2011, 07:23 PM

Hi tigertiger,

thanks for your help, but unfortunately it didn't work.
When I inserted the code and manually went through it, it worked fine, however when I ran the code automatically, I got a runtime error 1004, insert method of range class failed.

Why would it work manually but not automatically?

**atardif** · 07-24-2011, 12:03 AM

Hi Radiant, Michael

Thanks for your efforts, but no luck unfortunately.

I thought it might be useful for you to see the buildup to the code. Also, I am using MO 2011. The code is massive, but this is everything that appears before the insert column code. Basically this macro is sorting out a mass of data into some semblance of order - Sub MainC() is where the error occurs.

I have attached as a txt file

Thanks again
Alex

**johnjohns** · 07-24-2011, 03:15 AM

Perhaps a sample workbook could be of more help. We can then see what actually happens.

**snb** · 07-24-2011, 06:23 AM

Start clearing your code:

- do not use select
- do not use activate
- refer to the sheets unequivocally by using it's name e.g.: sheets("hotel') sheets("manager")
- dive into the VBEditor's help about loops : For...Next
- avoid errorhandlers