Macro to list worksheets and have an option to save selected sheets as separate workbook

[kittu55]

Hi,

I'm looking for a macro which list the total sheets in an active workbook and provide an option to select multiple worksheets at once and then save them as different workbooks.

I tried searching for it but all efforts are in Vain.

Can someone help on this.

Thanks in Advance.

Regards

[BellyGas]

Where are you searching, Ask Jeeves?

list all files in workbook (first result in Google)
http://www.ozgrid.com/forum/showthread.php?t=61138

You can save the list to a worksheet....use a simple method of sheet selection, like put a * in the next column, and then record yourself selecting and saving said sheets. Add a for next loop to look for your selection marker and.....job done.

Google 'VBA for next loops' for info on that.

[kittu55]

Hi Belly Gas

Thanks for the response.

The workbook will not remain the same. In this case i can't use the recording part

[kittu55]

Hi Belly Gas

Thanks for the response.

The workbook will not remain the same. In this case i can't use the recording part

[BellyGas]

You can use the recorder to get the correct format for the command. Then you can replace the part of the command that shows the workbook (ie c:\whereever\whereever\workbook.xlsx) and replace that with a string variable which you can either populate from a user input box or....any way you like really.

[bakerman2]

Example file list all sheets in multiselect Listbox.
Select the sheets you want to save and click button.
Selected sheets will be saved in DefaultPath (which you have to adapt before running code) with the sheetname as filename.

[kittu55]

Hi bakerman2

Thanks a ton for the help. This is what i was looking for.

However when i use the same code and try to create the same i'm unable to select multiple sheets at once. I'm not sure of what i'm missing.

Can you please help me with that.

Thanks

[bakerman2]

In the Properties window of the ListBox you have to set MultiSelect to 1 - fmMultiSelectMulti.