copy paste special values

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" <[email protected]> wrote in message
news:%[email protected]...
>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" <[email protected]> wrote in message
news:%[email protected]...
>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" <[email protected]> wrote in message
> news:%[email protected]...
>>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" <[email protected]> wrote in message
news:%[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> "mike allen" <[email protected]> wrote in message
> news:%[email protected]...
>>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" <[email protected]> wrote in message
news:[email protected]...

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" <[email protected]> wrote in message
> news:[email protected]...
>> "mike allen" <[email protected]> wrote in message
>> news:%[email protected]...
>>>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" <[email protected]> wrote in message
news:[email protected]...
> "Norman Jones" <[email protected]> wrote in message
> news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> "mike allen" <[email protected]> wrote in message
>>> news:%[email protected]...
>>>>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" <[email protected]> wrote in message
news:%[email protected]...

[...]
> 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