Jim,

How nice and clean that is.

However, here is the revised code, which now fails with the same run time
error, this time on the copy statement. An further thoughts?

Lee

Sheets("Morestats").Activate
Sheets("Morestats").Select
Set rnge = wb.Sheets("MoreStats").Range("A1")
Range(rnge, rnge.End(xlDown).End(xlToRight)).clear
Sheets("data").Activate
Sheets("data").Select
Set rnge = wb.Sheets("data").Range("A1")
Range(rnge, rnge.End(xlDown).End(xlToRight)).Copy **** run time error '1004'

"Jim Thomlinson" wrote:

Selects are problematic and there is really no need for them in this case (in
most cases actually)... Try something more like this

Set rnge = wb.Sheets("MoreStats").Range("A1")
Range(rnge, rnge.End(xlDown).End(xlToRight).clear
Set rnge = wb.Sheets("data").Range("A1")
Msgbox rnge.address
'Shows "$A$1
Range(rnge, rnge.End(xlDown).End(xlToRight)).Copy
--
HTH...

Jim Thomlinson


"Lee Hunter" wrote:

The following code excutes as shown, however the 2nd Range Select statement
receives run time error '1004'. When I manually select A1 of sheet "data"
and press cntl + end, cell "U53" is selected. What am I overlooking?

TIA,
Lee

Set rnge = wb.Sheets("MoreStats").Range("A1")
Range(rnge, rnge.End(xlDown).End(xlToRight)).Select
Selection.clear
Sheets("data").Select
Set rnge = wb.Sheets("data").Range("A1")
Msgbox rnge.address
'Shows "$A$1
Range(rnge, rnge.End(xlDown).End(xlToRight)).Select '*** This one fails
Selection.copy