Copy worksheet from one WB to another, overwrite existing worksheet with same CodeName

Hi, I'm looking for some help with the following:

The goal:
I've got multiple identical files ("UserBook") which are used by separate users. One specific copy of the same file ("MasterBook") is located on a shared drive. Each time a specific worksheet within MasterBook is updated, I need all the UserBook files to reflect the changes.

My attempt:
I've got it pretty much figured out. In each of the user files, upon Workbook_Open() I have it compare the date it was last updated (which it gets from a cell value on the worksheet) with the modification date of the master file. If the master file has been modified since the last time the user file was updated, then delete the specific worksheet on the userfile and replace it with the same worksheet from the MasterBook.

The problem:
Even though the UserBook worksheet is deleted BEFORE the MasterBook worksheet is copied over, the worksheet's CodeName is still changed from "wsTheList" to "wsTheList1". This is unacceptabe due to the CodeName being heavily used throughout the rest of the program.

My Code:

Please Login or Register  to view this content.

I tried changing the CodeName before deleting the worksheet:

Please Login or Register  to view this content.

This worked great and I thought I was done... but once the VBProject is password protected, this no longer works.

Any help on this would be much appreciated. I feel like I'm missing something simple...

Originally Posted by Renns

I feel like I'm missing something simple...

On the contrary, it will be hard. Officially, there is no way to deprotection of the project. Unofficial ways are uncertain. You may have to resign from references to sheets by CodeName.
But...
And if you need to replace the sheets? Can not you copy the data from UsedRange Master to UserSheet?

Artik

I apologize for my language. I use a translator.

Originally Posted by Richard Buttrey

Hi,

Can you upload the master and one user workbook. I'm finding it hard to understand the various sheet names and code names.

Here is a mock version of my project to demonstrate this feature. Just place both files in the same location, then open UserList.xlsm.

Thanks Richard.
Yeah, I changed that line to Worksheets("The List").Activate just for the sake of this example so it would run. In the actual project all worksheet references use CodeNames.

I have also been toying around with it, and have come to the same conclusion; it must be split into two separate macros. So I did something like this:

Please Login or Register  to view this content.

This way does work, but it leaves a step in the hands of the user. If they decide not to obey the message (or don't read it, as many don't), and if try using any of the features or running any of the macros which use this worksheet while it's deleted, everything will crash. I don't want any crashes in a polished product, so I'm going to have to bang my head against the wall a little longer before accepting this.

I tried throwing in a UserBook.Close True at the end, so at least they couldn't use the file if the worksheet is missing, and when they open it again it will complete the update... but apparently that still behaves as though the macro was never stopped.

In fact, that does something even weirder... the wsTheList1 worksheet appears again (bad), but this time there is also a ghost workbook with the CodeName wsTheList. It's right under ThisWorkbook in the Project Explorer and it has a workbook icon instead of a worksheet icon. It won't let me view it or do anything with it, not even remove it. Continuing to update the file in this way creates additional worksheets CodeNamed wsTheList2, wsTheList3, etc.

I've attached the new working files. Try it "as is" to see it work (with manual restarting)... then remove the ' in front of UserBook.Close and try it again to see what I mean.

New Plan...
I'll leave it the way it works, where it's up to the user to close the file and open it again.

However...
I'll throw in a few lines of code at the beginning of all macros which require that worksheet:

Please Login or Register  to view this content.

That should do it.

Artik:
Brilliant! That works perfectly and produces exactly the results I was looking for! Thank you so much.

Richard:
Yeah, I added the "close and re-open the file" bit, hoping to use UserFile.Close in order to prevent users from having access to the file while wsTheList was missing. Thanks for all your help, your method also works well. I'm going to use the .OnTime method though, as it allows the complete update to occur without interruption and with minimal user involvement.

Cheers!

Richard Buttrey,
For a long time I thought that it is impossible to pass parameters to a procedure with OnTime. A few years ago I found a solution, that I showed. You can pass parameters String and Numeric, but did not Object.

Originally Posted by Richard Buttrey

Well done.

Thx.

Artik

Originally Posted by Artik_PL

You can pass parameters String and Numeric, but not Object.

I was wondering why you passed the workbook.names as strings instead of just workbooks, so I played with it and sure enough. Just Strings and Numeric, eh? Good to know. Thanks again.

Originally Posted by Artik_PL

Attention! Recompile destroys all variables! If you are using global variables, you have to rebuild them before or after copying sheet.

Uh oh. I though I was in the clear because any Const variables still worked... BUT... after recompiling, things like "Worksheet_SelectionChange" no longer work. For example, the following code made comments appear whenever a cell with a comment was selected:

Please Login or Register  to view this content.

But after the recompile it no longer works. In fact, the comments don't appear at all until after I close and then re-open the file. Is there a way to make it work without restarting Excel?

At the moment I do not have a brilliant idea. The only thing comes to my head is to save the file and re-open the workbook with OnTime. Not restart Excel!
I still think.

I apologize for my language. I use a translator.

Artik

Ok, apparently copying the worksheet now removes ALL cell comments. I don't understand this. The worksheet in MasterBook has all comments, but when the ws is copied over to UserBook, the comments are all gone. They're not hidden, they just don't exist. WHy would copying the worksheet not copy the comments on that worksheet?

I am only having this problem in my large project. The small sample files DO work properly, including copying the comments over. But when the exact method is applied to the larger project, the comments do not get copied over. Furthermore, I get the same problem when I manually copy one cell that has a comment in it from the master workbook and paste it into a brand new workbook. The cell value and format are copied, but the comment is not copied. It worked before, but now it doesn't. WTF?

Ok, so apparently I am an idiot.
The worksheet is protected, therefore I had to unprotect it, then copy it over, then protect it again.

I had that in my previous code, but when I applied the OnTime method from the sample files I forgot to add that one line. Blargh.

I need some fresh air...

[Edit]And damn you for being so fast and helpful, Richard! I was just going to delete my last post to hide my stupidity, but you already saw it. lol.[/edit]