PasteSpecial error

[Guest]

Hi
This line of code brings up a "Compile error" - "Expected: end of statement"
with Paste highlighted,
what end of statement is it expecting i think I've tried them all.

ActiveCell.Offset(-4, -1).Resize(1, 29).Copy Destination:=ActiveCell.Offset _
(-4, -1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

From previous posts I've read it appears it's best to split these into 2
lines or place in a "With - End With.
Any advice very much appreciated.
I'm trying to tidy up my code and clear formulas not required three rows
back as I add new data to the sheet.
--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

 

[Nick Hodge]

Robert

Try

With ActiveCell.Offset(-4, -1)
.Resize(1, 29).Copy
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End With

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Sharad]

In the copy command you can not use PasteSpecial.
So the complier is expecting end of satement before the .PasteSpecial
part of your code.

You need to break it in to two lines as under:

ActiveCell.Offset(-4, -1).Resize(1, 29).Copy
ActiveCell.Offset(-4, -1).PasteSpecial xlPasteValuesAndNumberFormats

Sharad

 

[Guest]

Thankyou Nick
and seasons greatings to you and yours.

Bob C.

 

[Guest]

Thank you Sharad
Your comments comfirmed what I was slowly finding out.
Copy, Destination and Pastespecial don't go together in one line.

Seasons greatings and have a happy New Year 2005.

Regards
Bob C.