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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.