Thread: Can't get past Line 3 of code

Can someone point out my fault in line 3?
TIA,

Sub tester()
Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion ' Covers
Range A1:C10
srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3).Select << error
..... In Progress
Set drng = Worksheets("Sheet2").Range("A2")
End Sub

You can only select on the active sheet.

So maybe just adding

worksheets("Sheet1").select

would help

Jim May wrote:
>
> Can someone point out my fault in line 3?
> TIA,
>
> Sub tester()
> Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion ' Covers
> Range A1:C10
> srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3).Select << error
> .... In Progress
> Set drng = Worksheets("Sheet2").Range("A2")
> End Sub

--

Dave Peterson

It works for me - what's the error?

Jim May wrote:
> Can someone point out my fault in line 3?
> TIA,
>
> Sub tester()
> Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion ' Covers
> Range A1:C10
> srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3).Select << error
> .... In Progress
> Set drng = Worksheets("Sheet2").Range("A2")
> End Sub

Sub tester()
Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion
Worksheets("Sheet1").Select
srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3).Select
Set drng = Worksheets("Sheet2").Range("A2")
End Sub

would be my guess.

--
Regards,
Tom Ogilvy


"Jim May" wrote:

> Can someone point out my fault in line 3?
> TIA,
>
> Sub tester()
> Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion ' Covers
> Range A1:C10
> srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3).Select << error
> ..... In Progress
> Set drng = Worksheets("Sheet2").Range("A2")
> End Sub
>
>

Thanks Andrew;
I was stepping through the code (F8) while my Sheet2 was the
activesheet.
Cause (after your reply..
When I retested it and it worked << Cause My Sheet1 was then active <<
Must keep this in mind in the future

Why does the Activesheet matter?



"Andrew Taylor" <andrew.taylor@cantab.net> wrote in message
news:1152275119.743911.267280@m73g2000cwd.googlegroups.com:

> It works for me - what's the error?
>
> Jim May wrote:
>
> > Can someone point out my fault in line 3?
> > TIA,
> >
> > Sub tester()
> > Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion ' Covers
> > Range A1:C10
> > srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3).Select << error
> > .... In Progress
> > Set drng = Worksheets("Sheet2").Range("A2")
> > End Sub

Thanks Dave for "an important base rule" - unknown to me, till now.
Only how I can remember;
Have a great day !!
Jim

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44AE52D1.5F6453B1@verizonXSPAM.net:

> You can only select on the active sheet.
>
> So maybe just adding
>
> worksheets("Sheet1").select
>
> would help
>
> Jim May wrote:
>
> >
> > Can someone point out my fault in line 3?
> > TIA,
> >
> > Sub tester()
> > Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion ' Covers
> > Range A1:C10
> > srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3).Select << error
> > .... In Progress
> > Set drng = Worksheets("Sheet2").Range("A2")
> > End Sub
>
>
> --
>
> Dave Peterson

Another useful "rule" to be aware of is that it's almost always
unnecessary to select ranges before using them, and to do
so will usually make your code run much more slowly.

So in your example you could have done somewthing like:
Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion
Set srng2 = srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3)

and this doesn't depend on having the right sheet activated,though
it dos assume you're in the right workbook; but you can handle this
by (e.g.) :
Set srng =
Workbooks("data.xls").Worksheets("Sheet1").Range("A1").CurrentRegion

It's also good to assign the workbooks and sheets you're using to
variables:
e.g. set MyWb = Workbooks("data.xls").
set SourceSheet = MyWb.Worksheets("Sheet1")
set srng = SourceSheet.Range("A1").CurrentRegion

And just one more rule in case you're not aware of it
- ALWAYS use Option Explicit


hth
Andrew


Jim May wrote:
> Thanks Dave for "an important base rule" - unknown to me, till now.
> Only how I can remember;
> Have a great day !!
> Jim
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:44AE52D1.5F6453B1@verizonXSPAM.net:
>
> > You can only select on the active sheet.
> >
> > So maybe just adding
> >
> > worksheets("Sheet1").select
> >
> > would help
> >
> > Jim May wrote:
> >
> > >
> > > Can someone point out my fault in line 3?
> > > TIA,
> > >
> > > Sub tester()
> > > Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion ' Covers
> > > Range A1:C10
> > > srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3).Select << error
> > > .... In Progress
> > > Set drng = Worksheets("Sheet2").Range("A2")
> > > End Sub
> >
> >
> > --
> >
> > Dave Peterson

Andrew,
This is really good stuff to know; thank you
for taking the time to help me (with what
the books are not as clear on..).
Jim May

"Andrew Taylor" wrote:

> Another useful "rule" to be aware of is that it's almost always
> unnecessary to select ranges before using them, and to do
> so will usually make your code run much more slowly.
>
> So in your example you could have done somewthing like:
> Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion
> Set srng2 = srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3)
>
> and this doesn't depend on having the right sheet activated,though
> it dos assume you're in the right workbook; but you can handle this
> by (e.g.) :
> Set srng =
> Workbooks("data.xls").Worksheets("Sheet1").Range("A1").CurrentRegion
>
> It's also good to assign the workbooks and sheets you're using to
> variables:
> e.g. set MyWb = Workbooks("data.xls").
> set SourceSheet = MyWb.Worksheets("Sheet1")
> set srng = SourceSheet.Range("A1").CurrentRegion
>
> And just one more rule in case you're not aware of it
> - ALWAYS use Option Explicit
>
>
> hth
> Andrew
>
>
> Jim May wrote:
> > Thanks Dave for "an important base rule" - unknown to me, till now.
> > Only how I can remember;
> > Have a great day !!
> > Jim
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:44AE52D1.5F6453B1@verizonXSPAM.net:
> >
> > > You can only select on the active sheet.
> > >
> > > So maybe just adding
> > >
> > > worksheets("Sheet1").select
> > >
> > > would help
> > >
> > > Jim May wrote:
> > >
> > > >
> > > > Can someone point out my fault in line 3?
> > > > TIA,
> > > >
> > > > Sub tester()
> > > > Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion ' Covers
> > > > Range A1:C10
> > > > srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3).Select << error
> > > > .... In Progress
> > > > Set drng = Worksheets("Sheet2").Range("A2")
> > > > End Sub
> > >
> > >
> > > --
> > >
> > > Dave Peterson
>
>

Thks Tom, Good Guess!!
Jim

"Tom Ogilvy" wrote:

> Sub tester()
> Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion
> Worksheets("Sheet1").Select
> srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3).Select
> Set drng = Worksheets("Sheet2").Range("A2")
> End Sub
>
> would be my guess.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Jim May" wrote:
>
> > Can someone point out my fault in line 3?
> > TIA,
> >
> > Sub tester()
> > Set srng = Worksheets("Sheet1").Range("A1").CurrentRegion ' Covers
> > Range A1:C10
> > srng.Offset(1, 0).Resize(srng.Rows.Count - 1, 3).Select << error
> > ..... In Progress
> > Set drng = Worksheets("Sheet2").Range("A2")
> > End Sub
> >
> >