Delete NOT selected sheets/tabs

I have a macro that I want to run on the sheets that I have selected (via the tabs at the bottom)... I then want the macro to delete all of the other sheets that are not selected. Basically the current macro (among other things) converts formulas to values and then want to delete the sheet with all of the data that was originally referenced (contains social security numbers and other personal information that is needed for the formulas but want gone before sending the sheet).

I can find information on deleting selected sheets (that is easy) and other information on providing a pop-up to select the sheets you want to keep, but since the tabs are already selected at the bottom, really want to avoid the pop-up menu (where you could forget/miss to check something).

Anyone have any ideas? Would really like the whole macro to delete itself and save the file under the same name as a non-macro excel file (.xlsx) as well.

Thanks in advance for the help,

I like SNB's idea. However, there is another alternative -
You can a frontpage that lists the sheet names available in the workbook. A checkbox will be provided against name. The user can select the check box to retain the sheet. The rest will be deleted.

The file then can be saved with a different name. Or the tabs remaining can be moved to a new workbook and saved in a different file name.

This is albeit a lil longer method but is good presentation wise.

Originally Posted by snb

I'd use the copy command to create a new workbook only containing the selected sheets. That new workbook won't contain macros if the macro isn't part of the macromodule of one of the selected sheets.
The only thing you have to do is save the new workbook under a new name. Ergo 2 VBA-lines will do the trick.

This was the option I was thinking I was going to have to do, I was just hoping there might be a way to do it differently, but I know there are limits to what can be done...

This is what i had suggested earlier -

You can a frontpage that lists the sheet names available in the workbook. A checkbox will be provided against name. The user can select the check box to retain the sheet. The rest will be deleted.

The file then can be saved with a different name. Or the tabs remaining can be moved to a new workbook and saved in a different file name.

Originally Posted by arlu1201

This is what i had suggested earlier -

And I am trying a variation of your method, but just trying to figure out how to get the text string to be recognized as the array of the sheets.

I have tried a few different things but cannot get it to recognize my list of sheets to select.

On your macro file, you can have a list of sheets entered through a macro with a check box next to each one. The boxes you select will be retained, the rest deleted.

I do know the basics. I am thinking way beyond the basics, and that is probably the problem.

I currently have code that can create a list of the non-selected sheets as text/string and was wanting to have that string recognized as an array of the sheets.

For example:

Please Login or Register  to view this content.

The above code will output a text string of all the sheets. If I put "MsgBox sheetnames" I get a list of the sheet in the format:

Sheet1, Sheet2, Sheet3, ...

Is there a way to define this as an array that will be recognized by the Sheets command? I do not even know if there is.

I was also trying to modify the following to work, but I cannot get the Sheets(array).select to work.

Please Login or Register  to view this content.

I again get a "Run-time error '9': Subscript out of range" on the line to select the sheets with the following code. Do you see any errors?

Please Login or Register  to view this content.