Clever Geek Handbook

Runtime error '1004': Range Class method selection failed VBA 2003

I am trying to copy a column from one sheet to another. The code I am using is a recorded macro and it works fine until I connect it to a button. When I do this it gives "Runtime Error" 1004: Range class method selection failed

Here is the code and I don't see anything wrong with that. When I hit debug it highlights the second line.

Sheets("Count").Select
Columns("C:C").Select
Selection.Copy
Sheets("Add Invintory").Select
Range("b1").Select
ActiveSheet.Paste
Sheets("Count").Select
Sheets("Count").Columns("A:A").Select
Columns("A:A").Select
Selection.Copy
Sheets("Add Invintory").Select
Range("A1").Select
ActiveSheet.Paste

I do not know what's the problem. Please, help

+2


source to share


3 answers


You should always avoid using .Select

They are the root cause of errors :)

Are you trying to do this?



Sub Sample()
    Sheets("Count").Columns("C:C").Copy _
    Sheets("Add Invintory").Columns("B:B")

    Sheets("Count").Columns("A:A").Copy _
    Sheets("Add Invintory").Columns("A:A")
End Sub
+8


source


I think the problem is that you have written the code in another worksheet code module. If I am on Sheet 1 and write, for example,

Sheets("Sheet2").Select
Columns("A:A").Select


... then Excel assumes you are referencing the columns on Sheet1 as it treats the current sheet as the default. So you told Excel "select Sheet 2" then "select a column in sheet 1" ... which it cannot do, it gives an error message. The best solution would be not to use "Select" ... but you will still see in Siddharth's code that he had to explicitly reference the sheet addresses

Your source code would work if it was placed in the ThisWorkbook module. Code entry positions are explained at the end of this Excel Help :

+3


source


When you put vba code into "view sheet code". There definitely helps to use Application.Run ... to run a macro.

I had a problem, I was directly injecting the macro into the sheet code .. for a selection in another sheet it was claiming runtime error 1004 .. So I created the macro separately and then I put Application.Run my macro from the sheet code.

Works great;)

This app. Run also helps when you have a macro that is too large, which claims to pretend it can't be that big. You can easily split into several parts and then just run the applications one by one ..;)

0


source






More articles:


All Articles