Duplicate Entire Workbook (without saving & incl VeryHidden sheets)

[xtinct2]

Hi --

I'm trying to add lines to my code to duplicate the active workbook as a separate workbook.
Initially, I was just using 'ActiveWorkbook.Sheets.Copy' but I noticed that this function doesn't copy over sheets that are VeryHidden.

The only other alternative I know is to use 'ActiveWorkbook.SaveCopyAs'. However, I'd like to avoid a route that requires the file to be saved via code because the file path can change depending on the user, and I don't want the users to have to worry about defining their specific file destinations.

The new workbook doesn't need to include any of the vba modules from the original -- if there's a version of 'ActiveWorkbook.Sheets.Copy' that includes VeryHidden sheets that'd be perfect. However, if only a total duplication is possible (incl the modules) then that's fine as well.

Thank you!

-k

[BadlySpelledBuoy]

Why not unhide the sheets in code, copy to a new workbook then hide them again?

BSB

[xtinct2]

HI

I was hoping there'd be a simpler way (to do this, I'd have to loop through all sheets in the initial file to check/log their visibility properties so that I can set back to the original view). My file has a large number of sheets, so to do this would add a lot to the runtime.

Also, to go this route I'd have to navigate between the two documents, and if the newly created document is to remain unsaved, I'm not sure how to toggle back to the unsaved file whose filename could be different each time 'Book#.xls'.

Do you know of a way to accomplish my aim without having to toggle between workbooks?

Thank you!

[BadlySpelledBuoy]

The below code will loop through each sheet in your workbook and copy them to a new one.
If VeryHidden it will unhide it, copy it and make it VeryHidden again.
Visible and Hidden sheets will be copied as is.

Please Login or Register  to view this content.

BSB

[xtinct2]

This is great, thanks!

Just curious, why are you using 'ws.Visible = 1' and not '-1' (I didn't know the '1' property existed for sheet visibility)?

Thanks!

[BadlySpelledBuoy]

Basically because I'm a lazy typer.
1 = Visible
0 = Hidden
2 = VeryHidden

BSB

[xtinct2]

Ah haha, cool cool

Also, I just noticed that I can't use the above code because since the sheets are being copied individually, formulas, named ranges, & data validations still reference the old sheet, instead of referencing locally. Not sure if this puts us back at square one or not, but can you think of a way to remedy?

I'm starting to think 'ActiveWorkbook.SaveCopyAs' may be my only option

-k

[BadlySpelledBuoy]

OK, I can see why that could be a problem!

The SaveCopyAs approach could work. You can have VBA save to a My Documents folder or Desktop without the need to know the specific path.
I have to go out for a while so not time to guide further at the moment, but have a look at the link below on "special folders"

https://www.rondebruin.nl/win/s3/win027.htm

I'll be back later to check in and see how you get on.

BSB

[xtinct2]

Thanks

I think saving to desktop may be my backup plan.

I've found that I could just have a dialog box open for them to save it wherever they'd like

Please Login or Register  to view this content.

However, this overwrites the current file, and I'd prefer for the original to remain untouched.

If there was a way to let them save as something else, choosing the path/name via a dialog box, but still keep the original open, then I could live with that.

Think that's possible?

Thank you so much, btw!

[xtinct2]

Hi --

I've been searching and searching, and I don't think the exact thing I was looking for is possible.

I think I'm going to go w/ the below. This method saves the copy to the desktop, with a file name that's unique to the current date and time down to the second to avoid file name duplication errors. It then reopens that file from the user's desktop, and continues with running the code.

The user can rename/move later if they want.

Please Login or Register  to view this content.

Thanks again for all your assistance!

-k

[BadlySpelledBuoy]

What about the below? It will open the save as dialog box and if the user selects the same filename as the master copy it won't let them continue until they choose a different filename. Although really they shouldn't be able to as that would be an xlsm file and the code filters for xlsx files only.

Please Login or Register  to view this content.

BSB

[xtinct2]

Hi --

The only issue with this is that it closes the original file without saving it, and I wouldn't want the user to lose unsaved work (or force them to save the original workbook at that moment if they didn't want to).

Since there's not a dialog box for them to save the new file (and keep the original open & unsaved), I think the best workaround is to save it to the desktop (common folder for everyone) as a unique name (no filename duplication issues), then reopen and run the code from there.
I implemented this into my draft and I'm fine with it.

I'm not too familiar with CreateObject("WScript.Shell").SpecialFolders("Desktop") so I'm not sure if it'd work on a mac but I think the people who would be running it are all on PCs anyways -- I still prefer to make my code cross-compatible, but I dont need to get hung up on that just yet.

Thanks for being so patient/generous with your time!!

-k