excel vba insert column runtime error 1004

Question

this is my first post on StackExchange! I've been using StackExchange for answers, but now i really have a question.

I am trying to add a column in excel using vba. This is procedure is part of a bigger sub function of which I created a new workbook and copy a series of sheets from a different workbook over.

Workbooks.Add
Set TTB = ActiveWorkbook
'add a bunch of sheets here
'sheetName = specific_sheet
Set ttb_sheet = TTB.Sheets(sheetName)
ttb_sheet.Columns("I:I").Insert Shift:=xlToRight

With this i get a runtime error of 1004: 'Insert method of Range class failed'

I tried following a series of questions on StackOverflow...

Select method of Range class failed via VBA

VBA error 1004 - select method of range class failed

It seems like the solution is to select the sheet first, then select the range. I have tried this and there was no luck. Anyone have any insight?

Here's my main sub code..

Sub create_TTB_workbook(TTB_name_)
'create TTB workbook
Dim wsHelper As New WorksheetHelper
Dim final_TTB As Workbook
Dim ttb_sheet As Worksheet

ttb_wb = ActiveWorkbook.name
Workbooks(ttb_wb).Activate

PCB_tab = 0
ST_COMP_tab = 0

For Each WS In Worksheets

    If WS.name = "PCB_PIN_REPORT" Then
        PCB_tab = 1
    End If
    If WS.name = "ST_PIN_REPORT" Then
        ST_COMP_tab = 1
    End If
Next WS

Workbooks.Add
Set TTB = ActiveWorkbook
new_ttb_wb = TTB.name

Debug.Print (new_ttb_wb)

If PCB_tab = 1 Then
    wsHelper.copySheet ttb_wb, "PCB_PIN_REPORT", new_ttb_wb, "PCB_PIN_REPORT"
End If
If ST_COMP_tab = 1 Then
    wsHelper.copySheet ttb_wb, "ST_PIN_REPORT", new_ttb_wb, "ST_PIN_REPORT"
End If

wsHelper.copySheet ttb_wb, TTB_name_, new_ttb_wb, TTB_name_
' TRIED A BUNCH OF METHODS here...
'Workbooks(ttb_wb).Sheets(TTB_name_).Cells.copy
'Sheets.Add.name = TTB_name_
'ActiveSheet.paste

'Sheets(TTB_name_).Activate
'Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'Worksheets(TTB_name_).Range("I1").EntireColumn.Insert

'Columns("I:I").Select

'Columns("I:I").Insert Shift:=xlToRight

Set ttb_sheet = Sheets(TTB_name_)
ttb_sheet.Columns("I:I").Insert Shift:=xlToRight

Columns("K").copy Destination:=Range("I1")
Range("I6") = "header name"
End Sub

Have you somehow filled one or more rows with data all the way to column XFD? If you did, you should get a more detailed message that explained that you were trying to push data off the right-side of the worksheet. (btw, there is nothing wrong with your code once the gaps have been filled in and NO you do not have to select either a worksheet or a range before inserting a column) — user4039065
Jeeped, how would i check if my data is filled all the way to XFD? i visually checked this by looking at how much the horizontal scrollbar is filled up on the sheet. — huynle
You can try to add the column manually through the UI. If the sheet is filled up, you won't be able to add the column. If you can, that isn't your issue. — Jon Crowell
Try Shift+Ctrl+End to see where Excel thinks the last_cell is. — user4039065
You started with a brand spanking new workbook but have you locked the structure with worksheet or workbook protection in one of the steps that you omitted? — user4039065

Jon Crowell Jon Crowell · Accepted Answer · 2015-07-08T19:39:56

Whenever I run into an issue like this, I always isolate the code to its simplest form. Once I get it working at that level, I add it back in to the full application and can usually figure out what I did wrong.

I've written a simple version of what you are trying to do. Note that I've included a few Debug.Print statements to help me verify what is going on. The debug messages will appear in your Immediate window. Obviously you can also step through the code and examine variables as you go.

To get this to work, create a workbook and save it as DestinationWorkbook.xlsx. Then open another workbook and insert the code below.

Sub InsertColumnInDestinationWorksheet()
    Dim sourceWb As Workbook
    Dim targetWb As Workbook
    Dim sourceWs As Worksheet
    Dim targetWs As Worksheet

    Set sourceWb = ThisWorkbook
    Debug.Print sourceWb.Name

    Set targetWb = Workbooks("DestinationWorkbook.xlsx")
    Debug.Print targetWb.Name

    Set sourceWs = sourceWb.Sheets("Sheet1")
    Debug.Print sourceWs.Name

    Set targetWs = targetWb.Sheets("Sheet1")
    Debug.Print targetWs.Name

    targetWs.Range("I1").Value2 = "Moving right along..."
    targetWs.Columns("I:I").Insert shift:=xlToRight
End Sub

After running the code, you can examine the target sheet. You should see the text we wrote into column I is now in column J.

excel vba insert column runtime error 1004

2 Answers