PasteSpecial error
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 |
PasteSpecial error
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 |
PasteSpecial error
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! |
PasteSpecial error
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 |
PasteSpecial error
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! |
All times are GMT +1. The time now is 04:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com