What do I not see?
 

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

What do I not see?
 

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

What do I not see?
 

Typo Jim

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

RP
(remove nothere from the email address if mailing direct)


"Jim Thomlinson" wrote in message
...
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

What do I not see?
 

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

What do I not see?
 

Lets clean this up if we can. Without the typos this time :)

dim wksStats as worksheet
dim wksData as worksheet
dim rngStats as range
dim rngData as range

set wksStats = wb.Sheets("Morestats")
set wksData = wb.Sheets("Data")

with wksStats
set rngStats = .range(.range("A1"), .Range("A1").end(xldown).end(xlToRight))
rngstats.clear
end with

with wksData
set rngData = .range(.range("A1"), .Range("A1").end(xldown).end(xlToRight))
rngData.copy
end with

--
HTH...

Jim Thomlinson


"Lee Hunter" wrote:

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

What do I not see?
 

Thanks Jim,

Even cleaner and neater. Did the trick. Much appreciated.

Lee

"Jim Thomlinson" wrote:

Lets clean this up if we can. Without the typos this time :)

dim wksStats as worksheet
dim wksData as worksheet
dim rngStats as range
dim rngData as range

set wksStats = wb.Sheets("Morestats")
set wksData = wb.Sheets("Data")

with wksStats
set rngStats = .range(.range("A1"), .Range("A1").end(xldown).end(xlToRight))
rngstats.clear
end with

with wksData
set rngData = .range(.range("A1"), .Range("A1").end(xldown).end(xlToRight))
rngData.copy
end with

--
HTH...

Jim Thomlinson


"Lee Hunter" wrote:

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