Excel 2003 - VBA - .copy

[Craig Brandt]

I want to copy a range from one page to another. I seem that I have to have
the sheet that I am copying from as the activesheet.

Sheets("Display").Select
Sheets("PPO").Range(Cells(3, 2), Cells(3, NumSym + 1)).Copy
Sheets("Param").Cells(3, 2).PasteSpecial Paste:=xlPasteValues,
Transpose:=True

The above will fail.

Sheets("PPO").Select
Sheets("PPO").Range(Cells(3, 2), Cells(3, NumSym + 1)).Copy
Sheets("Param").Cells(3, 2).PasteSpecial Paste:=xlPasteValues,
Transpose:=True

This will work.

Is there a way to do this without selecting either sheet. When this
procedure runs, the sheets are flickering back and forth and I would like to
change parameter and watch the results on the display sheet without all this
distraction.

Thanks,

Craig

 

[Socko]

I never knew that the range wont be copied if the range is specified
with two cells... But try this,
Instead of referring to range as range(cells(i,j),cells(k,l)), try
doing range("a1:x6") and this will work. I am not sure why this
happens... but belive your problem is solved now.

Sheets("Display").Select
Sheets("PPO").Range("B3:" & Chr(64 + NumSym + 1) & "3").Copy
Sheets("Param").Cells(3, 2).PasteSpecial Paste:=xlPasteValues,
Transpose:=True

i hope this helps...

Selva V Pasupathy
For more on Excel, VBA, & other Resources
Please visit: http://socko.wordpress.com

 

[Andy Pope]

Hi,

If you fully qualify the range references it should work.

Sheets("PPO").Range(Sheets("PPO").Cells(3, 2), Sheets("PPO").Cells(3,
NumSym + 1)).Copy

Cheers
Andy

 

[Socko]

Thanks andy for sharing this...

Selva V Pasupathy
For more on Excel, VBA, & other Resources
Please visit: http://socko.wordpress.com