Macro that copies workseet into new book saves as broken unless converted first

Hello all!

I have been looking into making a macro that copies a sheet into a new workbook for a whole month. In perusing the site i found an old thread that had this same issue. With a little tweaking to make it suit my needs i was able to have it run the macro for all 14 sheets i need just fine. It isn't until after i ran all of them that i noticed i could not open them once closed. The error i would receive stated "Excel cannot open the file 'FILE NAME' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.". I then noticed if i go to save the file once ran before closing it i can convert it to the new format and i do not have this issue. Is there a macro that will automatically convert to the newest format and save so i do not have this issue again OR is there an old part of this code that needs updated?

Below find the code.

Please Login or Register  to view this content.

If you are using latest version of Excel (and assuming that you don't want to save as macro enabled xlsm files), you probably need to use Fileformat:=xlOpenXMLWorkbook

You can use FileFormat:=51 as a shorter method.

Originally Posted by shirleyxls

If you are using latest version of Excel (and assuming that you don't want to save as macro enabled xlsm files), you probably need to use Fileformat:=xlOpenXMLWorkbook

You can use FileFormat:=51 as a shorter method.

This was my exact problem! Thank you shirleyyxls, you've saved me a huge headache.

Glad to have helped.

Excel is a constant learning process and it's great to help each other. If I've helped you today, a click on the star on the left is appreciated.