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

word wrap got it should all be on one line or this
sheets("Sheet1").Range(Range("A1"), Range("B1").End(xlDown)).Copy _
sheets("Sheet2").Range("A1")



"Paul B" wrote in message
...
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

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

Dave, thanks, yes I wanted A1:bottom of B, and it was working to do that,
but I see that it can be done in a better way, thanks again
"Dave Peterson" wrote in message
...
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