PDA

View Full Version : Method 'Copy' of object'_Worksheet' failed PROBLEM



simora
09-18-2017, 03:55 AM
Trying to create a new workbook but the Method 'Copy' of object'_Worksheet' failed error keeps preventing the copy process.
I've attached a workbook. The Macro is run from the NewMODSheet WorkSheet.

The Button says " Add A New WorkBook"

Caution! The attached worksheet crashes when the Macro runs.
But at the bottom of the sheet it shows copying.
A new Book1 is created, but it's hidden from view, and the worksheet crashes.

The same code works on other sheets.

Aflatoon
09-18-2017, 04:42 AM
It doesn't crash for me, though of course you will get prompted about removing the VB project as you are trying to save a workbook with code in it as an xlsx file. Also, you really ought to specify the file format parameter.

simora
09-18-2017, 03:30 PM
Hi Aflatoon (http://www.vbaexpress.com/forum/member.php?24778-Aflatoon):

I have tried to save in every format I can think of, including


ActiveWorkbook.SaveAs "C:\GraphicSheetTest.xls", FileFormat:=56

The line that causing the problem is the Copy portion of the code;


Sheets(Array("NewMODSheet", "Sheet2")).Copy

I'm still not sure what's the problem.

mdmackillop
09-19-2017, 02:13 AM
Newer versions of Windows will not allow you to save a file to the Root directory. Try another folder.

simora
09-20-2017, 11:16 PM
mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop):

I have already tried that.
ActiveWorkbook.SaveAs "C:\Daily\GraphicSheetTest.xls", FileFormat:=56

Same exact error at the same location. Were you able to get it to save?

mdmackillop
09-21-2017, 03:38 AM
No issues with either of these:

ActiveWorkbook.SaveAs "C:\VBAX\GraphicSheetTest.xlsm", FileFormat:=52
ActiveWorkbook.SaveAs "C:\VBAX\GraphicSheetTest.xls", FileFormat:=56


As a last resort try

Worksheets(Array("NewMODSheet", "Sheet2")).Copy

simora
11-14-2017, 05:41 PM
Thanks mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop);

I was away from Computers for a while. Traveling & A minor medical issue. Now, back to work.