I need to copy a range to another area. I am trying:

Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Sheets("sheet2").Range(Cells(1, 1), Cells(3, 3)).Value

I can get it this way, but seems like too much code and slower. I have come
accustomed to using the above 1 liner, but this example, for some reason,
doesn't work.

Sheets("sheet2").Range(Cells(1, 1), Cells(3, 3)).Copy
Sheets("sheet1").Cells(1, 1).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

any thoughts? thanks, mike allen

i need to clarify. the top code in original message does NOT work, while
the bottom code does work. i have found a way to get the top to work, but
not sure why it works. i did the following to get the one line code to
work:
Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Range(Sheets("sheet2").Cells(1, 1), Sheets("sheet2").Cells(3, 3)).Value

while the following does not work:
Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Sheets("sheet2").Range(Cells(1, 1), Cells(3, 3)).Value

why would this work and not the mirror image of the destination range? thx

"mike allen" wrote in message
...
I need to copy a range to another area. I am trying:

Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Sheets("sheet2").Range(Cells(1, 1), Cells(3, 3)).Value

I can get it this way, but seems like too much code and slower. I have
come accustomed to using the above 1 liner, but this example, for some
reason, doesn't work.

Sheets("sheet2").Range(Cells(1, 1), Cells(3, 3)).Copy
Sheets("sheet1").Cells(1, 1).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

any thoughts? thanks, mike allen

alright. i guess i got it. it looks like to be consistent, i need to use:
Range(Sheets("sheet1").Cells(1, 1), Sheets("sheet1").Cells(3, 3))=
Range(Sheets("sheet2").Cells(1, 1), Sheets("sheet2").Cells(3, 3)).Value
where this IS a mirror image, i just had the syntax wrong on both sides
originally. thx

"mike allen" wrote in message
...
i need to clarify. the top code in original message does NOT work, while
the bottom code does work. i have found a way to get the top to work, but
not sure why it works. i did the following to get the one line code to
work:
Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Range(Sheets("sheet2").Cells(1, 1), Sheets("sheet2").Cells(3, 3)).Value

while the following does not work:
Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Sheets("sheet2").Range(Cells(1, 1), Cells(3, 3)).Value

why would this work and not the mirror image of the destination range? thx

"mike allen" wrote in message
...
I need to copy a range to another area. I am trying:

Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Sheets("sheet2").Range(Cells(1, 1), Cells(3, 3)).Value

I can get it this way, but seems like too much code and slower. I have
come accustomed to using the above 1 liner, but this example, for some
reason, doesn't work.

Sheets("sheet2").Range(Cells(1, 1), Cells(3, 3)).Copy
Sheets("sheet1").Cells(1, 1).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

any thoughts? thanks, mike allen

"mike allen" wrote in message
...
i need to clarify. the top code in original message does NOT work, while
the bottom code does work. i have found a way to get the top to work, but
not sure why it works. i did the following to get the one line code to
work:
Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Range(Sheets("sheet2").Cells(1, 1), Sheets("sheet2").Cells(3, 3)).Value

while the following does not work:
Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Sheets("sheet2").Range(Cells(1, 1), Cells(3, 3)).Value

Try this:
Sheets("Sheet6").[N24:P26] = Sheets("Sheet2").[H238:J240].Value
or this:
Sheets("Sheet6").Range("N24:P26") =
Sheets("Sheet2").Range("H238:J240").Value

They both work with Excel 2003

Bruno

Hi Bruno,

I know from your posts, especially elsewhere, that you are an avid proponent
of abbreviation, especially the square bracket syntax.

If you are not aware, you may find the following MS Knowledge Base article
of interest:

'Square Bracket Notation Is Less Efficient Than Tunneling'
http://support.microsoft.com/default...b;en-us;104502


---
Regards,
Norman


"Bruno Campanini" wrote in message
...
"mike allen" wrote in message
...
i need to clarify. the top code in original message does NOT work, while
the bottom code does work. i have found a way to get the top to work, but
not sure why it works. i did the following to get the one line code to
work:
Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Range(Sheets("sheet2").Cells(1, 1), Sheets("sheet2").Cells(3, 3)).Value

while the following does not work:
Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Sheets("sheet2").Range(Cells(1, 1), Cells(3, 3)).Value

Try this:
Sheets("Sheet6").[N24:P26] = Sheets("Sheet2").[H238:J240].Value
or this:
Sheets("Sheet6").Range("N24:P26") =
Sheets("Sheet2").Range("H238:J240").Value

They both work with Excel 2003

Bruno

"Norman Jones" wrote in message
...

Hallo Norman,
I found of interest the article you mentioned mainly for the
fact I knew it is also possible to use [Sheet!A1:A10] instead of the
normal tunneling notation.
I know I can't use variables in square braket notation, but this
is not a problem.

Efficiency:
Tunnelling notation is some micro-seconds
faster then square braket notation? Is it a problem?

Further:
The writer says the article applies to
Microsoft Excel 97 Standard Edition.
Well, I don't actually have any Excel 97/2000 installed,
but I'm quite sure square braket notation is not allowed
in Excel 97/2000.
May be my memory fails on this point; do you have any
chance to check it?

Summarizing:
Having acquired just a little experience in using a product
such as Excel, the strait way to dive deeper and deeper is
your own experimentation rather than studying theoretical
srticles, even thogh they are written by MS.
My opinion, of course.

Btw Norman, thanks very much for info.

Bruno




Hi Bruno,

I know from your posts, especially elsewhere, that you are an avid
proponent of abbreviation, especially the square bracket syntax.

If you are not aware, you may find the following MS Knowledge Base article
of interest:

'Square Bracket Notation Is Less Efficient Than Tunneling'
http://support.microsoft.com/default...b;en-us;104502


---
Regards,
Norman


"Bruno Campanini" wrote in message
...
"mike allen" wrote in message
...
i need to clarify. the top code in original message does NOT work, while
the bottom code does work. i have found a way to get the top to work,
but not sure why it works. i did the following to get the one line code
to work:
Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Range(Sheets("sheet2").Cells(1, 1), Sheets("sheet2").Cells(3, 3)).Value

while the following does not work:
Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Sheets("sheet2").Range(Cells(1, 1), Cells(3, 3)).Value

Try this:
Sheets("Sheet6").[N24:P26] = Sheets("Sheet2").[H238:J240].Value
or this:
Sheets("Sheet6").Range("N24:P26") =
Sheets("Sheet2").Range("H238:J240").Value

They both work with Excel 2003

Bruno

Hi Bruno,

Tunnelling notation is some micro-seconds
faster then square braket notation? Is it a problem?

That depends on the number of evaluation calls made by the programme. In
trivial code, I accept that the timing difference would be imperceptible.

but I'm quite sure square braket notation is not allowed
in Excel 97/2000.
May be my memory fails on this point; do you have any
chance to check it?

The square bracket notation is certainly available in both xl97 and xl2000.
In respect of xl97, your own quote

The writer says the article applies to Microsoft Excel 97 Standard Edition

indicates that. As regards, xl2k, I ran your square bracket code on an xl2k
system without problem.

Having acquired just a little experience in using a product
such as Excel, the strait way to dive deeper and deeper is
your own experimentation rather than studying theoretical
srticles, even thogh they are written by MS.
My opinion, of course.

Experimentation and a healthy degree of cynicism are, in my opinion,
estimable virtues. Devise, therefore, some test code which makes intensive
use of square bracket evaluation calls; run this code, and analogous code
without such evaluation calls, with a timer and compare the results.

Finally, my comments were not made in any spirit of criticism, but uniquely
in reponse to an observation that your invariable penchant is for the square
bravket notational style.

---
Regards,
Norman


"Bruno Campanini" wrote in message
...
"Norman Jones" wrote in message
...

Hallo Norman,
I found of interest the article you mentioned mainly for the
fact I knew it is also possible to use [Sheet!A1:A10] instead of the
normal tunneling notation.
I know I can't use variables in square braket notation, but this
is not a problem.

Efficiency:
Tunnelling notation is some micro-seconds
faster then square braket notation? Is it a problem?

Further:
The writer says the article applies to
Microsoft Excel 97 Standard Edition.
Well, I don't actually have any Excel 97/2000 installed,
but I'm quite sure square braket notation is not allowed
in Excel 97/2000.
May be my memory fails on this point; do you have any
chance to check it?

Summarizing:
Having acquired just a little experience in using a product
such as Excel, the strait way to dive deeper and deeper is
your own experimentation rather than studying theoretical
srticles, even thogh they are written by MS.
My opinion, of course.

Btw Norman, thanks very much for info.

Bruno




Hi Bruno,

I know from your posts, especially elsewhere, that you are an avid
proponent of abbreviation, especially the square bracket syntax.

If you are not aware, you may find the following MS Knowledge Base
article of interest:

'Square Bracket Notation Is Less Efficient Than Tunneling'
http://support.microsoft.com/default...b;en-us;104502


---
Regards,
Norman


"Bruno Campanini" wrote in message
...
"mike allen" wrote in message
...
i need to clarify. the top code in original message does NOT work,
while the bottom code does work. i have found a way to get the top to
work, but not sure why it works. i did the following to get the one
line code to work:
Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Range(Sheets("sheet2").Cells(1, 1), Sheets("sheet2").Cells(3, 3)).Value

while the following does not work:
Sheets("sheet1").Range(Cells(1, 1), Cells(3, 3)) =
Sheets("sheet2").Range(Cells(1, 1), Cells(3, 3)).Value

Try this:
Sheets("Sheet6").[N24:P26] = Sheets("Sheet2").[H238:J240].Value
or this:
Sheets("Sheet6").Range("N24:P26") =
Sheets("Sheet2").Range("H238:J240").Value

They both work with Excel 2003

Bruno

"Norman Jones" wrote in message
...

[...]
Finally, my comments were not made in any spirit of criticism, but
uniquely in reponse to an observation that your invariable penchant is for
the square bravket notational style.

And with such a spirit was intended by me.

Thanks again Norman for nice conversation.
Bruno