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

0
votes

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.

3
vbaexcel
it's because your sheet Inspection Report isn't active at the moment macro runs. Read how to avoid using Select/Active statements, 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
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
Actually, I can't understand what are you tring to do here: Shift:=x1 + nToRight? should it be Shift:=xlToRight? – Dmitry Pavliv

3 Answers

1
votes

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!

1
votes

You must be writing code on different sheet referencing different.

Try to write the same in Module.

0
votes

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