compatibility mode and new file creation

miconian

Well-known Member
Joined
Aug 18, 2004
Messages
768
I have an .xls file (compatibility mode) containing about 40 worksheets. My goal is to save each worksheet as a separate workbook. To do this, I turned to David Mcritchie's macro, found here: http://www.mvps.org/dmcritchie/excel/saveas.htm

I run this macro in Excel 2007. It works, but I get the compatibility prompt before each save. As you can imagine, this takes forever and basically defeats the time-saving purpose of using the macro to begin with.

In order to get around this, I have experimented with various combinations of which format (xls, xlsx, xlsm) to use for the original file, and also to designate for the ending of the new files, as shown within the VBA code. This includes saving the original file as .xlsm, closing it, opening it up again, and running the macro then, with the macro either in its original form, or modified to save a different type of file for the new workbooks.

But no matter what I do, the macro only gets as far as creating the very first workbook, and then giving an error that I'm trying to paste into a workbook that doesn't have as many rows as the original workbook. Of course, the new workbook is being created by the macro.

I would very much appreciate suggestions, and would be happy to provide more information, or try different solutions. Thanks!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
Have you tried disabling alerts

Code:
Application.DisplayAlerts = False
Then do your save, then
Code:
Application.DisplayAlerts = True
 

miconian

Well-known Member
Joined
Aug 18, 2004
Messages
768
I tried adding "Application.DisplayAlerts = False" as the very first line of code in the module, and it had no effect; the alerts still happened. I then saved the workbook as .xlsm, closed it, opened it up, and ran the macro again, still with that line of code added. As before, I got an error that I'm trying to move data to a new worksheet that doesn't have as many rows as the old worksheet. I changed the VBA code so that the new worksheets would be created with the .xlsm extension, but that had no effect; the error still occurred.
 

miconian

Well-known Member
Joined
Aug 18, 2004
Messages
768

ADVERTISEMENT

I tried it with both 52 and 51, and in each case I tried saving to an xls, and xlsx, and an xlsm, and the result was always the same as before.

According to the debugger, the error is happening on this line:

CurWkbook.Worksheets(wkSheet.Name).Copy Before:=newWkbook.Sheets(1)
 

dscg

Active Member
Joined
Apr 28, 2004
Messages
367
We should also remove the part that specifies .xls in the filename:

Code:
ActiveWorkbook.SaveAs _
         filename:=xpathname & wkSheetName, _
         FileFormat:=52, Password:="", _
         WriteResPassword:="", CreateBackup:=False, _
         ReadOnlyRecommended:=False

Tested on Vista/Excel 2007 and ran w/out a hitch after that correction. Post back the entire code you have if you keep having problems and maybe someone will be able to point out the issue.


'
 

miconian

Well-known Member
Joined
Aug 18, 2004
Messages
768
Just tried that. Still getting runtime error 1004 (trying to put data into new workbook with fewer rows than old workbook).

Not sure if it matters, but at this point my source workbook is saved as an xlsm.
 

miconian

Well-known Member
Joined
Aug 18, 2004
Messages
768
FYI, it does create the file based on the very first tab, and that file is saved as an .xlsm file in compatibility mode. Apparently the error occurs when trying to put the data into the new file.
 

Forum statistics

Threads
1,136,326
Messages
5,675,116
Members
419,551
Latest member
thangxpm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top