Split workbook into worksheets

Hi,
I have got an Excel workbook with a few sheets in. I would like to split the workbook so that each separate sheet is transferred to a new workbook using VBA (in other words, split a workbook into many workbooks) and I would like to be able to choose where to save the new workbooks when the macro is running (meaning, open the save as dialogue).

Please advise me what to do.

Many thanks.

thanks, I get a "Run-time error '1004' Application-defined or object-defined error" when I run the macro. Please advise what to do.

Many thanks in advance.

I want all of them to be saved in a folder which I specify whilst the macro is running (show the save as dialog). The name of the workbook should be the name of sheet which was copied to it. It should me saved in *xlsx format. Please advise what do do.

Many thanks.

If I recall correctly if you do

Please Login or Register  to view this content.

and do not provide any arguments it copies to a new workbook. you can then assign activeworkbook to a variable and name it, save it, close etc.

So as was posted prior, loop the worksheets collection in your source workbook and use worksheet.copy (were "worksheet" is the worksheet object) to create a new workbook from each sheet. Then do what you like with them and close them within the loop once complete.

you can use workbook.saveas to pass it a path and name to save to (can derive from source workbook or a path you determine statically or dynamically).

Can you please show me all the code together as I am fairly new to VBA.

Many thanks.

Originally Posted by Chaverim

Can you please show me all the code together as I am fairly new to VBA.

Many thanks.

Please dont take this the wrong way but I am here to help, not do it for people. It would go a long way if you posted a sample file (or even 2, a before and after), any existing code you may have and elaborated on your requirements (ie: do you want a dialog for each extracted sheet or 1 to decide where they all go, what file type you want to save as: xlsx, xlsm, xlsb, xls, etc., what do you do if a file by that name exists in the destination already?, any other considerations/requirements).

When I split a workbook into other files I also typically check that the file names are valid names (not containing symbols which arent valid for example). by posting the above you ensure someone like myself doesnt waste their time and your time writing code that doesnt meet your needs.

Thanks for the quick reply. Firstly, I want one dialog for all the sheets. Secondly, I dont mean this in a bad way either, but if you had looked above at a previous thread that i wrote, i said that i wanted it in xlsx format.

Many thanks.

X-posted
https://www.mrexcel.com/forum/excel-...orksheets.html

The code is too long. i have see much shorter codes but you cant choose where to save or save them all in one go. Please advise what to do.

Many tahnks