Copy Workbook Sheets, Keep Certain Sheets, Set Visibility

[xtinct2]

Hi --

Looking for VBA ideas
I have a file with a large amount of sheets. To create an end-user version, I'd like to do the following:

1. Copy all sheets to new workbook
2. Delete sheets not listed in specified table (say, Table1)
3. Set visibility properties of remaining sheets per defined value in Table1

Table1 is a pre-populated excel table that lists a couple sheet names (Table1[Sheets to Keep]), and has a column next to each sheet name with either -1, 0, or 2 (Table1[Sheet Visibility]).

Only the sheets I wish to keep are listed in Table1, all other sheets are to be deleted.

What would you suggest as the best/most efficient way to approach #s 2 and 3 above?

Thank you!

-k

[Mumps1]

It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.

[xtinct2]

Hi --

Please see attached

No changes should be made to any contents on any of the sheets.

Steps needed are:

1. Sheets to be copied to a separate workbook (all sheets, so that no dependant formulas on a sheet reference the original file).
   For this I was planning to just use 'ActiveWorkbook.Sheets.Copy'.
2. Sheets not listed in Table1 to be deleted
3. Remaining sheets visibility properties to be set via the value in 'Sheet Visibility' column (-1 = Visible, 0 = Hidden, 2 = Very Hidden)

Note that in the original file, some sheets are Hidden & Very Hidden.
Also, the new file doesn't need to be Saved or Saved As anything, you can keep as Book1, or whatever Excel defaults to.

Please let me know if you have any other questions/comments.
Thank you!

-k

[Mumps1]

This was a little more complicated than I thought. Try this macro:

Please Login or Register  to view this content.

[xtinct2]

Thanks for looking into it!

I looked at what you sent and also took a stab at a solution that's a bit more legible for intermediate users, like myself.
The only remaining issue is that 'ActiveWorkbook.Sheets.Copy' doesn't seem to copy over VeryHidden sheets, so my only solution may be to use 'ActiveWorkbook.SaveCopyAs' instead.
(I was hoping to avoid this since the destination file path could be different, depending on who's running the code)

If you can think of a way to duplicate the entire file (incl. VeryHidden sheets) without having to save the resulting workbook, that'd be great. If not, all good.
In case you were interested, here's where I landed (note that I changed 'Sheet X's visibility to -1 since that sheet has the reference table and needs to be visible).:

Please Login or Register  to view this content.

[Mumps1]

Is everything working for you now?

[Vraag en antwoord]

Possibly so?

Please Login or Register  to view this content.

[xtinct2]

Hi --

This doesn't seem to duplicate the file, only perform the deletions within the existing file which is no longer an issue for me.
The outstanding question is whether I can duplicate an entire file (incl. Very Hidden sheets & without moving the sheets one-by-one), all while keeping the original file open/unsaved.
I'm not familiar w/ some of the functions you're using so it's not clear if yours accomplishes this aim, but it doesn't seem that it does.

Thank you