I have a macro to copy data and a picture from one workbook (A) to another workbook (B). Workbook B is in Excel 2010. The code works fine if workbook A is an earlier version of Excel such as 97-2003. But if workbook A is also in Excel 2010 format, the code seems to work but upon trying to save the file I get an error ("Errors were detected while saving 'C:...'. Microsoft Excel may be able to save the file by removing or repairing some features...").
The key part of the code (after opening workbook A) is:
For Each s In workbookA.Sheets
If s.Pictures.Count > 0 Then
For Each pic In s.Shapes
If Left(pic.Name, 3) = "Pic" Then
The error is resolved by deleting the picture. Or by saving and closing Workbook B prior to closing Workbook A. Neither of those is a great solution. From my research it sounds like this is an error within Excel 2010 where you can't copy pictures between spreadsheets generally rather than a VBA issue. There might be a patch, but corporate policies and the number of users who will need to run the macro make that a bad option for me.
Is there a good way to get around this? Some way to copy a picture between Excel 2010 spreadsheets that doesn't cause this problem? Thanks.