You have some unqualified ranges in your statement. Unqualified ranges in
general modules point at the active sheet. But unqualified ranges in a
worksheet module point at that worksheet.

This portion:
sheets("Sheet1").Range(Range("A1"), Range("B1").End(xlDown)).Copy
could be rewritten as:

sheets("sheet1").range(sheets("sheet1").range("a1" ), _
sheets("sheet1").range("b1")).end(xldown).copy

Notice each Range has a qualified worksheet in front of it.

But to save your fingers:

With Worksheets("sheet1")
.Range(.Range("a1"), .Range("b1")).End(xlDown).Copy _
Destination:=Worksheets("sheet2").Range("a1")
End With

or even:

With Worksheets("sheet1")
.Range("a1:b1").End(xlDown).Copy _
Destination:=Worksheets("sheet2").Range("a1")
End With

(Just a curiosity, when I did this, it was pretty much the same as using A1 by
itself. Is that what you wanted?)

If you wanted A1:bottom of B, then maybe:

With Worksheets("sheet1")
.Range("a1", .Range("b1").End(xlDown)).Copy _
Destination:=Worksheets("sheet2").Range("a1")
End With


Paul B wrote:

I can run this from any sheet and it works
sheets("Sheet1").Range("A1:A5").Copy sheets("Sheet2").Range("A1")

So why will this run from sheet 1 but when I run it from another sheet I get
a run time error 1004? And how can this be fixed?
sheets("Sheet1").Range(Range("A1"), Range("B1").End(xlDown)).Copy
sheets("Sheet2").Range("A1")

Thanks

--

Dave Peterson