[code]
Worksheets("Sheet1").Select (or Activate)
[end code]

causes a breakout from Application.ScreenUpdating=false

So I am trying to reference the range I need using:

[code]
Worksheets("Sheet1").Range("J2", Range("J2").End(xlDown)). Copy
Worksheets("Sheet2").Range("b2")
[end code]

but I get an object error. I need to reference the range with .End because
the size of range starting at J2 will keep changing.

All/any help greatly appreciated & sorry for the double post (?)

Joe

Joe,

I assume you are on another sheet when you get this error? I think it is
because that in these circumstances the first Range refers to Sheet1, but
the second is not fully qualified, so it refers to the active sheet.

Try

With Worksheets("Sheet1")
.Range("J2", .Range("J2").End(xlDown)).Copy
End With


not the dots before the Range keywords.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Joe 90" wrote in message
...
[code]
Worksheets("Sheet1").Select (or Activate)
[end code]

causes a breakout from Application.ScreenUpdating=false

So I am trying to reference the range I need using:

[code]
Worksheets("Sheet1").Range("J2", Range("J2").End(xlDown)). Copy
Worksheets("Sheet2").Range("b2")
[end code]

but I get an object error. I need to reference the range with .End because
the size of range starting at J2 will keep changing.

All/any help greatly appreciated & sorry for the double post (?)

Joe

Bob

Your extra "dots" worked a treat, and this will help me enormously with my
coding. just wish I properly understood the science behind it, although I
see what you say about the second range not being qualified.

Joe90

Calling from the Wild Hills of Winchester!



"Bob Phillips" wrote in message
...
Joe,

I assume you are on another sheet when you get this error? I think it is
because that in these circumstances the first Range refers to Sheet1, but
the second is not fully qualified, so it refers to the active sheet.

Try

With Worksheets("Sheet1")
.Range("J2", .Range("J2").End(xlDown)).Copy
End With


not the dots before the Range keywords.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Joe 90" wrote in message
...
[code]
Worksheets("Sheet1").Select (or Activate)
[end code]

causes a breakout from Application.ScreenUpdating=false

So I am trying to reference the range I need using:

[code]
Worksheets("Sheet1").Range("J2", Range("J2").End(xlDown)). Copy
Worksheets("Sheet2").Range("b2")
[end code]

but I get an object error. I need to reference the range with .End
because
the size of range starting at J2 will keep changing.

All/any help greatly appreciated & sorry for the double post (?)

Joe

The science behind the With...End With structure is pretty simple:

1) The "." is used as shorthand for whatever follows the With, so


With Worksheets("Sheet1")
.Range("J2", .Range("J2").End(xlDown)).Copy
End With

is shorter, and perhaps faster to write than

Worksheets("Sheet1").Range("J2", _
Worksheets("Sheet1").Range("J2").End(xlDown)).Copy

2) In the second example, the Worksheets("Sheet1") reference has to
be resolved twice. Using With...End With, the worksheets("Sheet1")
reference is only resolved once. Resolving object (Workbook,
Worksheet, Range, etc.) references is very time and resource
consuming, so the With...End With structure is also more efficient
any time you have more than one reference to an object.


In article ,
"Joe 90" wrote:

Your extra "dots" worked a treat, and this will help me enormously with my
coding. just wish I properly understood the science behind it, although I
see what you say about the second range not being qualified.