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

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

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

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

"Dave Peterson" wrote in message
:

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" wrote in message
:

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" wrote in message
:

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

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

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