2

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 so, it gives a

Run Time Error '1004': Select method of Range Class failed

Here is the code and I can see nothing wrong with it. 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 have no clue what the problem is. Please help

Mesut Akcan
  • 899
  • 7
  • 19
Stephan Daudt
  • 119
  • 3
  • 4
  • 11

3 Answers3

8

You should always avoid using .Select They are a major cause of errors. You may want to see How to avoid using Select in Excel VBA

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
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
3

I think the issue is that you have written the code in another sheet's code module. If I'm in Sheet1, and write e.g.

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

...then Excel assumes you are referring to the Columns on Sheet1 as it treats the current sheet as a default. Therefore, you've told Excel "select Sheet 2" then "select a column on Sheet 1"...which it can't do so it gives you an error message. The best solution would be not to use 'Select'...but you will still see in Siddharth's code that he has had to refer to sheet addresses explicitly

Your original code would have worked if placed in the ThisWorkbook module. Locations for entering code are explained towards the end of this Excel help video

Ed Bolton
  • 936
  • 6
  • 4
0

When you are putting vba code into the "view sheet code" .. There definitely helps to use Application.Run ... to run macro..

I had problem i directly input macro to sheet code.. for selection in another sheet it claimed runtime error 1004.. So i created macro separately and then i put Application.Run my macro from sheet code.

Works out perfectly ;)

This Application.Run also helps when you have too big macro that excel claim it cant be so big. You can easily divide to several parts and then just run applications one by one.. ;)

tomas
  • 1