Excel 2007 : Paste and PasteSpecial

**Rick_Stanich** · 06-04-2008, 06:25 PM

I don't see the problem? Bob Philips originally helped with this project and everything worked fine, now the macro will not paste data from one workbook to another.

The code, yea its ugly, just look at it with one eye and it aint so bad.

Please Login or Register  to view this content.

This bit "'ActiveSheet.Paste 'paste cells from original worksheet" is the code to paste which fails

Error: Run-time error 1004
Paste method of Worksheet class failed

I am just copying cells A1 thru I102 and paste to another workbook in the same range. This macro worked fine until, dare I say it, the dreaded SP3. <sends shivers up my spine>

As always, any help is appreciated.
(Spell checker isnt working so you might want to close both eyes)

**Rick_Stanich** · 06-04-2008, 06:37 PM

P.S.
PasteSpecial did not work either.

**Leith Ross** · 06-04-2008, 06:39 PM

Hello Rick,

Was this written in Excel 2003 and your now running it on Office 2007?

Sincerely,
Leith Ross

**Rick_Stanich** · 06-05-2008, 09:57 AM

Hi Leith
The code was written in Excel 2007.

**Rick_Stanich** · 06-05-2008, 10:29 AM

Something I failed to take notice of, some of the files this macro will open are Excel 2003. With that, this macro did infact perform properly on thousands of files from either Excel 2003 or 2007.
The code runs through complete if I block the Paste line of code.

**Leith Ross** · 06-05-2008, 10:33 AM

Hello Rick,

I don't have 2007 yet. If you don't get an answer to your question soon, I will move your post to the 2007 Help forum.

Sincerely,
Leith Ross

**Rick_Stanich** · 06-05-2008, 11:57 AM

You may not want Excel 2007

Please move this to the 2007 forum, sorry I did not look for a specific forum. I will be more aware next time and I'm sure there will be a next time.

Thank you

**Rick_Stanich** · 06-06-2008, 03:36 PM

I have managed a work around for this. Having to utilize two seperate macros and workbooks for Excel 2007 and earlier versions due to worksheet format issues.
Excel 2003 or earlier; requires workbooks and or worksheets in "xls" formatting.

Please Login or Register  to view this content.

To use Excel 2007 simply change this line of code "If Right(sFileName, 3) = "xls" Then 'check for "xls" suffix" to "If Right(sFileName, 4) = "xlsx" Then 'check for "xlsx" suffix" and use Excel 2007 workbooks and or worksheets.

Hopefully this helps some one other than myself.