Worksheet or Me. Why the difference?

I

IanC

I have some code which is confusing me. The code copies some named ranges
from Lookup to specific locations on Checklist.

This code works fine:

With Worksheets("Checklist")
[Lookup!InstruSoftware].Copy .Range("I2")
[Lookup!TubeCount].Copy .Range("U3")
[Lookup!Alpha_kV].Copy .Range("A6")
[Lookup!Alpha_AEC].Copy .Range("A18")
[Lookup!Alpha_Errors].Copy (.Range("O6")) 'xxxx
[Lookup!AlphaAlignment].Copy .Range("Y6")
[Lookup!Alpha_Mechanical].Copy (.Range("Y15")) 'xxxx
[Lookup!Alpha_Electrical].Copy (.Range("Y30")) 'xxxx
[Lookup!Alpha_Earthing].Copy (.Range("O28")) 'xxxx
End With

If I change Worksheets("Checklist") to Me, I get an error for each of the
marked lines. The error is "Run-time error '1004': Copy method of Range
class failed".

The destination sheet is unprotected and there are no merged cells in the
destination ranges.

Can anyone explain why there is this difference between using Me and the
sheet name?
 
B

Bob Phillips

You can only use Me when there is a containing object (Me) in question, such
as in Userform code (Me if the form), in ThisWorkbook code module (Me is the
workbook), or in a worksheet code module (Me is the worksheet).
 
I

IanC

Hi Bob

Thanks for the response.

The code is contained in the worksheet, so Me should equate to
Worksheets("Checklist") and appears to in some cases. I can't understand why
it works for some named ranges, but not others.

Any ideas?

--
Ian
--

Bob Phillips said:
You can only use Me when there is a containing object (Me) in question,
such
as in Userform code (Me if the form), in ThisWorkbook code module (Me is
the
workbook), or in a worksheet code module (Me is the worksheet).

--

HTH

Bob

IanC said:
I have some code which is confusing me. The code copies some named ranges
from Lookup to specific locations on Checklist.

This code works fine:

With Worksheets("Checklist")
[Lookup!InstruSoftware].Copy .Range("I2")
[Lookup!TubeCount].Copy .Range("U3")
[Lookup!Alpha_kV].Copy .Range("A6")
[Lookup!Alpha_AEC].Copy .Range("A18")
[Lookup!Alpha_Errors].Copy (.Range("O6")) 'xxxx
[Lookup!AlphaAlignment].Copy .Range("Y6")
[Lookup!Alpha_Mechanical].Copy (.Range("Y15")) 'xxxx
[Lookup!Alpha_Electrical].Copy (.Range("Y30")) 'xxxx
[Lookup!Alpha_Earthing].Copy (.Range("O28")) 'xxxx
End With

If I change Worksheets("Checklist") to Me, I get an error for each of the
marked lines. The error is "Run-time error '1004': Copy method of Range
class failed".

The destination sheet is unprotected and there are no merged cells in the
destination ranges.

Can anyone explain why there is this difference between using Me and the
sheet name?
Click to expand...
Click to expand...
 
B

Bob Phillips

It should apply to them all if they are ranges within that worksheet, if
another worksheet then they won't apply.

--

HTH

Bob

IanC said:
Hi Bob

Thanks for the response.

The code is contained in the worksheet, so Me should equate to
Worksheets("Checklist") and appears to in some cases. I can't understand
why it works for some named ranges, but not others.

Any ideas?

--
Ian
--

Bob Phillips said:
You can only use Me when there is a containing object (Me) in question,
such
as in Userform code (Me if the form), in ThisWorkbook code module (Me is
the
workbook), or in a worksheet code module (Me is the worksheet).

--

HTH

Bob

IanC said:
I have some code which is confusing me. The code copies some named ranges
from Lookup to specific locations on Checklist.

This code works fine:

With Worksheets("Checklist")
[Lookup!InstruSoftware].Copy .Range("I2")
[Lookup!TubeCount].Copy .Range("U3")
[Lookup!Alpha_kV].Copy .Range("A6")
[Lookup!Alpha_AEC].Copy .Range("A18")
[Lookup!Alpha_Errors].Copy (.Range("O6")) 'xxxx
[Lookup!AlphaAlignment].Copy .Range("Y6")
[Lookup!Alpha_Mechanical].Copy (.Range("Y15")) 'xxxx
[Lookup!Alpha_Electrical].Copy (.Range("Y30")) 'xxxx
[Lookup!Alpha_Earthing].Copy (.Range("O28")) 'xxxx
End With

If I change Worksheets("Checklist") to Me, I get an error for each of
the
marked lines. The error is "Run-time error '1004': Copy method of Range
class failed".

The destination sheet is unprotected and there are no merged cells in
the
destination ranges.

Can anyone explain why there is this difference between using Me and the
sheet name?
Click to expand...
Click to expand...
Click to expand...
 
I

IanC

Hi Bob

I thought that should be the case. All the names ranges are on the Lookup
sheet and referenced as such, but it's wierd that some apply correctly and
others fail. I can't see what the difference is.

Oh well. It looks like I'll have to be happy sticking with
Worksheets("Checklist").

--
Ian
--
Bob Phillips said:
It should apply to them all if they are ranges within that worksheet, if
another worksheet then they won't apply.

--

HTH

Bob

IanC said:
Hi Bob

Thanks for the response.

The code is contained in the worksheet, so Me should equate to
Worksheets("Checklist") and appears to in some cases. I can't understand
why it works for some named ranges, but not others.

Any ideas?

--
Ian
--

Bob Phillips said:
You can only use Me when there is a containing object (Me) in question,
such
as in Userform code (Me if the form), in ThisWorkbook code module (Me is
the
workbook), or in a worksheet code module (Me is the worksheet).

--

HTH

Bob

I have some code which is confusing me. The code copies some named
ranges
from Lookup to specific locations on Checklist.

This code works fine:

With Worksheets("Checklist")
[Lookup!InstruSoftware].Copy .Range("I2")
[Lookup!TubeCount].Copy .Range("U3")
[Lookup!Alpha_kV].Copy .Range("A6")
[Lookup!Alpha_AEC].Copy .Range("A18")
[Lookup!Alpha_Errors].Copy (.Range("O6")) 'xxxx
[Lookup!AlphaAlignment].Copy .Range("Y6")
[Lookup!Alpha_Mechanical].Copy (.Range("Y15")) 'xxxx
[Lookup!Alpha_Electrical].Copy (.Range("Y30")) 'xxxx
[Lookup!Alpha_Earthing].Copy (.Range("O28")) 'xxxx
End With

If I change Worksheets("Checklist") to Me, I get an error for each of
the
marked lines. The error is "Run-time error '1004': Copy method of Range
class failed".

The destination sheet is unprotected and there are no merged cells in
the
destination ranges.

Can anyone explain why there is this difference between using Me and
the
sheet name?
Click to expand...
Click to expand...
Click to expand...
 
J

Jon Peltier

All the lines you've marked as failed have an extra set of parentheses
around the range. Coincidence?

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
774-275-0064
http://peltiertech.com/


Hi Bob

I thought that should be the case. All the names ranges are on the Lookup
sheet and referenced as such, but it's wierd that some apply correctly and
others fail. I can't see what the difference is.

Oh well. It looks like I'll have to be happy sticking with
Worksheets("Checklist").

--
Ian
--
Bob Phillips said:
It should apply to them all if they are ranges within that worksheet, if
another worksheet then they won't apply.

--

HTH

Bob

IanC said:
Hi Bob

Thanks for the response.

The code is contained in the worksheet, so Me should equate to
Worksheets("Checklist") and appears to in some cases. I can't understand
why it works for some named ranges, but not others.

Any ideas?

--
Ian
--

You can only use Me when there is a containing object (Me) in question,
such
as in Userform code (Me if the form), in ThisWorkbook code module (Me is
the
workbook), or in a worksheet code module (Me is the worksheet).

--

HTH

Bob

I have some code which is confusing me. The code copies some named
ranges
from Lookup to specific locations on Checklist.

This code works fine:

With Worksheets("Checklist")
[Lookup!InstruSoftware].Copy .Range("I2")
[Lookup!TubeCount].Copy .Range("U3")
[Lookup!Alpha_kV].Copy .Range("A6")
[Lookup!Alpha_AEC].Copy .Range("A18")
[Lookup!Alpha_Errors].Copy (.Range("O6")) 'xxxx
[Lookup!AlphaAlignment].Copy .Range("Y6")
[Lookup!Alpha_Mechanical].Copy (.Range("Y15")) 'xxxx
[Lookup!Alpha_Electrical].Copy (.Range("Y30")) 'xxxx
[Lookup!Alpha_Earthing].Copy (.Range("O28")) 'xxxx
End With

If I change Worksheets("Checklist") to Me, I get an error for each of
the
marked lines. The error is "Run-time error '1004': Copy method of Range
class failed".

The destination sheet is unprotected and there are no merged cells in
the
destination ranges.

Can anyone explain why there is this difference between using Me and
the
sheet name?
Click to expand...
Click to expand...
Click to expand...
 
I

IanC

Hi Jon

No coincidence whatsoever! That was the problem. Now sorted.

Many thanks.

--
Ian
--

Jon Peltier said:
All the lines you've marked as failed have an extra set of parentheses
around the range. Coincidence?

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
774-275-0064
http://peltiertech.com/


Hi Bob

I thought that should be the case. All the names ranges are on the Lookup
sheet and referenced as such, but it's wierd that some apply correctly
and
others fail. I can't see what the difference is.

Oh well. It looks like I'll have to be happy sticking with
Worksheets("Checklist").

--
Ian
--
Bob Phillips said:
It should apply to them all if they are ranges within that worksheet, if
another worksheet then they won't apply.

--

HTH

Bob

Hi Bob

Thanks for the response.

The code is contained in the worksheet, so Me should equate to
Worksheets("Checklist") and appears to in some cases. I can't
understand
why it works for some named ranges, but not others.

Any ideas?

--
Ian
--

You can only use Me when there is a containing object (Me) in
question,
such
as in Userform code (Me if the form), in ThisWorkbook code module (Me
is
the
workbook), or in a worksheet code module (Me is the worksheet).

--

HTH

Bob

I have some code which is confusing me. The code copies some named
ranges
from Lookup to specific locations on Checklist.

This code works fine:

With Worksheets("Checklist")
[Lookup!InstruSoftware].Copy .Range("I2")
[Lookup!TubeCount].Copy .Range("U3")
[Lookup!Alpha_kV].Copy .Range("A6")
[Lookup!Alpha_AEC].Copy .Range("A18")
[Lookup!Alpha_Errors].Copy (.Range("O6")) 'xxxx
[Lookup!AlphaAlignment].Copy .Range("Y6")
[Lookup!Alpha_Mechanical].Copy (.Range("Y15")) 'xxxx
[Lookup!Alpha_Electrical].Copy (.Range("Y30")) 'xxxx
[Lookup!Alpha_Earthing].Copy (.Range("O28")) 'xxxx
End With

If I change Worksheets("Checklist") to Me, I get an error for each of
the
marked lines. The error is "Run-time error '1004': Copy method of
Range
class failed".

The destination sheet is unprotected and there are no merged cells in
the
destination ranges.

Can anyone explain why there is this difference between using Me and
the
sheet name?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

To Set or not to Set that is the question 4

Top