Excel VBA: Run-time error '1004': Select method of Range class failed

Question

I am writing a macro to work in an excel sheet that is saved within a template then exported to .xls or .xlsx from a separate application. This macro is to copy two columns 'x' number of times depending on a quantity that is entered into a cell.

Sub Matrix_Quantity()
Dim x As Integer
x = ActiveWorkbook.Sheets("Inspection Sampling Matrix").Cells(11, 4)
Dim n As Integer
    n = x - 1
    For numtimes = 1 To n
        'Loop by using x as the index number to make x number copies.
        Sheets("Inspection Report").Columns("F:G").Select
        Selection.Copy
        Selection.Insert Shift:=x1 + nToRight
    Next
End Sub

The problem I am having is that when the macro is run withing the template (.xlt) it runs fine. As soon as the template is converted to .xls or .xlsx it spots working and gives a runtime error. When debugging the macro it highlights

Sheets("Inspection Report").Columns("F:G").Select

My feeling is that it is looking to select the columns in the .xlt workbook but when converted to .xls or .xlsx it is still trying to look for the .xlt workbook and I'm not sure how or why its doing this.

it's because your sheet `Inspection Report` isn't active at the moment macro runs. Read [**how to avoid using Select/Active statements**](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select), please. You could replace your code with `Sheets("Inspection Report").Columns("F:G").Copy` and `Sheets("Inspection Report").Columns("F:G").Insert Shift:=x1 + nToRight` — Dmitry Pavliv, Mar 05 '14 at 19:22
Thanks for the response. When I plug in the code you recommended and run the macro Excel will crash with no error. It simply brings up the windows error "Microsoft Excel has Stopped Working" — user3384820, Mar 05 '14 at 19:37
Actually, I can't understand what are you tring to do here: `Shift:=x1 + nToRight`? should it be `Shift:=xlToRight`? — Dmitry Pavliv, Mar 05 '14 at 19:56

score 1 · Answer 1 · answered Oct 07 '14 at 20:14

1

When I tried to run a script to split workbooks and save them as separate files with their workbook names, I received runtime error 1004 because I had one of the tabs hidden.

Make sure you UNHIDE all tabs before running a split workbook script!

answered Oct 07 '14 at 20:14

Papersquid

11
1

score 1 · Answer 2 · answered Nov 25 '14 at 22:11

1

You must be writing code on different sheet referencing different.

Try to write the same in Module.

answered Nov 25 '14 at 22:11

Manu

11
1

I added the lines: `code sheet.Activate (Where sheet is the Worksheet I'm using) sheet.Range("A1").Select code` And it got rid of the error. Excel switches to the work sheet for a second before continuing which is annoying but not that big of a deal. – Doctor Parameter Sep 01 '15 at 21:55

score 0 · Answer 3 · answered Apr 29 '14 at 15:12

I was having the same kind of problem and simoco's answer got me on the right track. This should work:

Sub Matrix_Quantity()
Dim n As Integer
Dim numtimes As Integer
n = Sheets("Inspection Sampling Matrix").Cells(11, 4) - 1
For numtimes = 1 To n
    Sheets("Inspection Report").Columns("F:G").Copy
    Sheets("Inspection Report").Columns("F:G").Insert Shift:=xlShiftToRight
Next
End Sub

Excel VBA: Run-time error '1004': Select method of Range class failed

3 Answers3