Excel VBA giving run-time error '1004' - Select method of Range class failed

[dfs3000my]

Hi

I am trying to copy contents of an Excel file to another for computation purposes. However, I'm receiving a run-time error '1004' - Select method of Range class failed.

Below is my code, please kindly provide some feedback:

'Open selected file
        Dim wb As Workbook
        Dim ws As Worksheet
        Set ws = Sheets("ICERawData")
        Set wb = Workbooks.Open(LATAMFilename)
        
        wb.Worksheets(1).Cells.Copy
        ws.Range("A1").PasteSpecial
        wb.Close
            
        Rows("1:4").Select
        Selection.Delete Shift:=xlUp
        Columns("C:AL").Select
        Selection.Delete Shift:=xlToLeft

Anybody have any idea?

[alfredo72]

the same error was given to me, with this syntax:

Columns("F:F").Select
Selection.Insert Shift:=xlToRight

Curiously, it worked on another computer!

[DiviNe187]

Sorry i have to bump this old thread up but.. alfredo, for some reason i have this exact same problem. I am using a simple logic for testing such as:

While counter < 100

    Count = (Str(counter))
    Sheets(Array("Blad1", "Blad2", "Blad3")).Select
    Sheets("Blad1").Activate
    Sheets(Array("Blad1", "Blad2", "Blad3")).Copy After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = Count + "A"
    Sheets(Sheets.Count - 1).Name = Count + "B"
    Sheets(Sheets.Count - 2).Name = Count + "C"
            
    counter = counter + 1

Wend

The weird thing is, yesterday ive had it end up in almost the same error, the only difference is that it says "copy range method failed"

This ofcourse has to do with the method im running, but the weird thing is, if i run this on a faster computer then needed (WINXP + office 2003 = slow) (win7 + office 07 = fast) it will result in finishing the method without any pain or error.

I have been reading about this and some state that sometimes these things get triggered by the fact of having a disk/computer that cannot keep up, for some reason when i use a method that is completely 100% the same on the faster computer i can work out sheets untill the end of the month.. though if i use that same exact method on the slower pc it will stop after day 11 (per day 3 new sheets) and throw me this error.

Did anyone ever noticed any similarities?
And if so, did you fixed it eventually?

[nebbishd]

I'm having a very similar problem. The code will sometimes work, but usually not work...the commands that are causing a problem are:

Worksheets("Schedule").Columns(i).Select
            Selection.EntireColumn.Hidden = True

I'm suspecting that this is actually a bug in VBA for Excel 2010.

[DiviNe187]

I doubt that it is a bug tho, it must be something that is incompatible inside VBA2010 since for me it seems to actually work fine on 2010 but not on 2003 or lower.

It always ends up stopping at a specific page (inside a copy + renaming algorithm), if it would stop randomly i would consider it to be a loose bug but this is something consistent.

I would suggest you to try and get excel 2003 running (for example in a virtual OS) to try and see if that one does the job, and if all else fails you still have 97 too wich should do just fine.

[DiviNe187]

After re-reading your post again i thought i'd add something:

If you can, try to run the solution with the Worksheet Column defined to one that you know that is filled and works. So instead of using the i identifier just add a 0 or 1 or 2 etc whatever meets your requirement, and see if it works then.. if it does, then your identifier is giving wrong values.

[nebbishd]

I've tried all sorts of variations...and actually, I got it to be error-free by first doing this:

Worksheets("Schedule").Activate

So, I'm not sure if there is a functional reason why I'd need to do that in order to show/hide columns on a sheet, but that's the workaround I used.

[janlars]

Hi, the solution can be to delete some columns before creating a new. Secect some columns by left-clicking the column-names (R, S, T etc.) and then rightclick within the marked area and Delete!