What do I not see?

[Lee Hunter]

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

[Jim Thomlinson]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Lee Hunter]

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

[Jim Thomlinson]

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

[Lee Hunter]

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