run-time error 1004

[Jerry]

This code runs fine on Office XP, but when I try to run it on an
Office 2000 machine I get a run-time error 1004 unable to paste
because the copy and paste area are not the same. I have searched high
and low to try and fix this on the 2000 machine but so far I have been
unsuccessful. I am hoping to tap the guru pool for help on this one.
Any suggestions would be greatly appreciated! Here is the offending
code:

'***************************************************
'Clean column "A" by removing character returns.
'***************************************************
Range("Q2").Select
r = Cells(Rows.Count, "A").End(xlUp).Row
Range("Q2").FormulaR1C1 = "=CLEAN(RC[-16])"
Range("Q2").AutoFill Destination:=Range("Q2:Q" & r)
Range("Q2:Q65536").Copy
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
I get the error on Range(Selection, Selection.End(xlDown).Select

 

[Dave Peterson]

Since you're copying Q2:q65536, why not just paste into A2:a65536?

But since you're filling from Q2 to the last used cell in column A, why not just
copy that range?

range("q2:q" & r).copy

And I find it easier not to match the range size. Just pick up the topleft cell
and let excel resize anything it needs to:

range("q2:q" & r).copy
range("a2").pastespecial ....

This code runs fine on Office XP, but when I try to run it on an
Office 2000 machine I get a run-time error 1004 unable to paste
because the copy and paste area are not the same. I have searched high
and low to try and fix this on the 2000 machine but so far I have been
unsuccessful. I am hoping to tap the guru pool for help on this one.
Any suggestions would be greatly appreciated! Here is the offending
code:

'***************************************************
'Clean column "A" by removing character returns.
'***************************************************
Range("Q2").Select
r = Cells(Rows.Count, "A").End(xlUp).Row
Range("Q2").FormulaR1C1 = "=CLEAN(RC[-16])"
Range("Q2").AutoFill Destination:=Range("Q2:Q" & r)
Range("Q2:Q65536").Copy
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
I get the error on Range(Selection, Selection.End(xlDown).Select

 

[Jerry]

Since you're copying Q2:q65536, why not just paste into A2:a65536?

But since you're filling from Q2 to the last used cell in column A, why not just
copy that range?

range("q2:q" & r).copy

And I find it easier not to match the range size. Just pick up the topleft cell
and let excel resize anything it needs to:

range("q2:q" & r).copy
range("a2").pastespecial ....

This code runs fine on Office XP, but when I try to run it on an
Office 2000 machine I get a run-time error 1004 unable to paste
because the copy and paste area are not the same. I have searched high
and low to try and fix this on the 2000 machine but so far I have been
unsuccessful. I am hoping to tap the guru pool for help on this one.
Any suggestions would be greatly appreciated! Here is the offending
code:

'***************************************************
'Clean column "A" by removing character returns.
'***************************************************
Range("Q2").Select
r = Cells(Rows.Count, "A").End(xlUp).Row
Range("Q2").FormulaR1C1 = "=CLEAN(RC[-16])"
Range("Q2").AutoFill Destination:=Range("Q2:Q" & r)
Range("Q2:Q65536").Copy
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
I get the error on Range(Selection, Selection.End(xlDown).Select

Thanks Dave! That makes the code much cleaner and easier to work with.
It worked like a champ! Thanks again.