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

Robert

Try

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Robert Christie" wrote in message
...
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

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thankyou Nick
and seasons greatings to you and yours.

Bob C.

"Nick Hodge" wrote:

Robert

Try

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Robert Christie" wrote in message
...
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

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.

"Sharad" wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!