Code won't work?

  • Thread starter Thread starter Joe 90
  • Start date Start date
J

Joe 90

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)
 
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 said:
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 said:
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
[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
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.
 
Back
Top