1

It seems pretty easy, but not able to find workaround.

Excel 2010. Aim: adding 22 empty columns starting from B

Error: Run-time error '1004': Insert method of Range class failed

Code:

  Application.DisplayAlerts = False 
  Application.ScreenUpdating = False 
  Sheets(1).Select
  ActiveSheet.Tab.ColorIndex = xlColorIndexNone

  Dim WbPresent As Workbook
  Set WbPresent = ActiveWorkbook

  Dim FileData As String
  FileData = "160125"

  Dim TabName As String
  TabName = "IB Hire Plan - Hierarchy " & FileData


   Workbooks.Open Filename:= _
     "\\chca6030.eur.beluni.net\m137557$\Documents\Makro   VBA\ATR\Taleo\*.xlsm"
   Sheets("IB Hire Plan - Hierarchy").Select
   Sheets(3).Activate

  Sheets("IB Hire Plan - Hierarchy").Copy Before:=WbPresent.Sheets(1)
  Sheets("IB Hire Plan - Hierarchy").Select

  For colx = 2 To 23
  Columns(colx).Insert Shift:=xlToRight
  Next*

Was checking similar question, but did not find the good answer for that.

Community
  • 1
  • 1
qcp
  • 113
  • 1
  • 2
  • 9
  • 3
    ① Are you trying to insert 22 columns all in one block? If so then something like `.Cells(1, 2).Resize(1, 22).EntireColumn.Insert` would be better. If the inserted columns are not intended to be contiguous, then loop backwards; not forwards. ② Use [With ... End With](https://msdn.microsoft.com/en-us/library/wc500chb.aspx) blocks to identify the parent worksheet of the cells you are referencing and the same for the parent workbooks of the worksheets you are referencing. ③ How much data is on the worksheet? Is it possible that you are trying to push data off the right side of the worksheet? –  Feb 01 '16 at 21:29
  • 1
    Is the file you're opening enabled for editing? Or read only? Also, I'm not sure the filename parameter for Workbooks.Open accepts wildcards (see [link](http://stackoverflow.com/questions/19527415/using-a-wildcard-to-open-an-excel-workbook)). – D_T Feb 02 '16 at 00:43
  • @Jeeped - funy thing is that it works and does not work at the same time. Your line of code is definietely fine because I am one step forward, see your solution on the screen but still the same issue on the screen. I guess it is all about references between files & sheets etc. – qcp Feb 13 '16 at 13:02
  • @D-T - thanks for your respond, actually the wildcard are fine, simply just after opening the file I need to press "enable for editing" plus it contains links to the other docs. That is the case I guess. – qcp Feb 13 '16 at 13:04
  • This was solved for me when I unprotected the sheet. – JohnG79 May 07 '18 at 06:49

1 Answers1

0

You haven't referenced the worksheet you wish to insert the columns in

Columns(colx).Insert Shift:=xlToRight

should be

ws.Columns(colx).Insert Shift:=xlToRight

where ws is the work sheet reference

amiles
  • 1
  • 3