Copy error despite previously working for months

[Luke777]

Hi all,
I've used the following code a few hundred times for the last few months (and its worked perfectly) - I've seen this error about twice.

"error 1004 Method range of object_worksheet failed" - error on *** line

    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set wb1 = Workbooks("MyWorkbook.xlsm")
    Set wb2 = Workbooks.Open(FileName:="MyOtherWorkbookURL.xlsx")
    Set ws1 = wb1.Worksheets(7)
    Set ws2 = wb2.Worksheets("MyOtherWorkbookSheet")
    
    ws2.Range(("A2:E2"), Range("A2:E2").End(xlDown)).Copy ***
    ws1.Range("C2").PasteSpecial Paste:=xlPasteValues
    ws1.Columns("D:F").EntireColumn.Delete
    wb2.Close

I do not understand why its happy to work 98% of the time - I'm wondering if it has anything to do with somebody else being in the workbook I'm accessing at the time, but I'm sure that's happened before and its been fine. There's no restrictions on the workbook aside from location (which I have access to).

From what I can tell, its opening wb2 absolutely fine, but its opening to the wrong sheet - even though the name of the sheet matches perfectly - and trying to copy an area that doesn't make much sense. But *why* is it only giving me this error a couple of times out of months of usage? It worked perfectly yesterday and for weeks previously, but not at all today no matter how many times I run it.

Is there a method I can use that *should* be more reliable?

Thanks all

 

[RoryA]

You need to qualify the second Range call with the same worksheet:

ws2.Range(("A2:E2"), ws2.Range("A2:E2").End(xlDown)).Copy

 

[Alex Blakenburg]

I also found the use of xldown on A2:E2 to be a bit misleading since it only uses column A to work out the bottom row.

ws2.Range("A2:E" & ws2.Range("A2").End(xlDown).Row).Copy

 

[Luke777]

You need to qualify the second Range call with the same worksheet:

ws2.Range(("A2:E2"), ws2.Range("A2:E2").End(xlDown)).Copy

surely this would mean it fails every time? Not works like 98% of the time? But I shall add that in thank you

 

[Luke777]

I also found the use of xldown on A2:E2 to be a bit misleading since it only uses column A to work out the bottom row.

ws2.Range("A2:E" & ws2.Range("A2").End(xlDown).Row).Copy

I understand this is probably bad practice, but for the data I have, data in A means data will exist in the row to E and if there's no data in A but there is data in E, E becomes usless anyway so copying it is pointless - if that makes sense.

Out of curiosity, how would you go about working out the bottom row?

 

[Fluff]

I would use

ws2.Range("A2:E" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Copy

using xlDown, means if there is no data in A3 or below, you will be copying A2:E1048576

surely this would mean it fails every time?

Your code would only fail if ws2 was not the active sheet

 

[Alex Blakenburg]

It sounds like your issue is mainly around ws2 but do you really need to refer to Worksheet(7) in ws1 ?
You indicated that other people use your workbook and if they move sheet 7 or add a sheet to the left of it, your macro won't work.
Can you not use the Sheet name or the CodeName of the sheet ?

 

[Luke777]

I would use

ws2.Range("A2:E" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Copy

using xlDown, means if there is no data in A3 or below, you will be copying A2:E1048576

Your code would only fail if ws2 was not the active sheet

Ahh I see, in my situation there's always data in that particular range (it gets updated *irregularly* by other users) - and there's actually a whole different set of data a few lines beneath where I'm copying which I don't want. It's not ideal for sure, but that's the situation I've unfortunately got to deal with when people decide to use Excel almost like a bulletin board lol

 

[Fluff]

Ahh I see, in my situation there's always data in that particular range

In that case using xlDown is fine.

 

[Luke777]

It sounds like your issue is mainly around ws2 but do you really need to refer to Worksheet(7) in ws1 ?
You indicated that other people use your workbook and if they move sheet 7 or add a sheet to the left of it, your macro won't work.
Can you not use the Sheet name or the CodeName of the sheet ?

luckily I'm the only person with access to wb1/ws1 and I won't be adding any sheets to disrupt the order, its the other workbook that people have access to - though I guess I should use the codename to avoid such errors as best practice.