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

[Renns]

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:

Sub UpdateTheList()
    Dim LastUpdated As Date         'The last time the user's file was updated.
    Dim MasterDate As Date          'The last time the master file was modified.
    Dim MasterFilePath As String    'The location of the master file.
    Dim MasterFileName As String    'The filename of the master file.
    Dim UserBook As Workbook        'The user's copy of the file.
    Dim MasterBook As Workbook      'The master copy of the file.
    Dim SheetIndex As Integer       'The index number of the worksheet being replaced.

'Note:  "wsTheList" is the CodeName of the worksheet being replaced.
    
    MasterVerseFilePath = "C:\Master File\"
    MasterVerseFileName = "MasterFile.xlsm"
    MasterDate = Format(FileDateTime(MasterFilePath & MasterFileName), "yyyy-mm-dd")
    LastUpdated = Format(wsTheList.Range("D2").Value, "yyyy-mm-dd")
    
'Compare the dates.
    If MasterDate > LastUpdated Then    'If the worksheet needs to be updated...
        Application.ScreenUpdating = False
        wsTheList.Activate                  'Go to the worksheet that is being replaced.
        Set UserBook = ActiveWorkbook       'Identify the user's file.
        SheetIndex = wsTheList.Index        'Mark the location of the existing wsTheList worksheet.
        Workbooks.Open (MasterFilePath & MasterFileName)    'Open the master file.
        Set MasterBook = ActiveWorkbook     'Identify the master file.
'Delete the old worksheet in the user's file.
        UserBook.Activate                   'Go back to the user's file.
        Application.EnableEvents = False
        Application.DisplayAlerts = False
        wsTheList.Delete
        Application.DisplayAlerts = True
        Application.EnableEvents = True
'Copy the worksheet from the master file to the user's file.
        MasterBook.Worksheets(Code2Name(MasterBook, "wsTheList")).Copy Before:=UserBook.Sheets(SheetIndex)
        MasterBook.Close False  'Close the Master file, do not save changes.
        wsTheList.Unprotect "LockItUp"
        wsTheList.Range("D2") = Format(Now(), "mmmm d, yyyy")    'Update the "Last Updated" date.
        wsTheList.Protect "LockItUp"
'Cleanup.
        Set UserBook = Nothing      'Uninitialize
        Set MasterBook = Nothing    'Uninitialize
        Application.StatusBar = False   'Return control of the Status Bar to Excel.
        Application.ScreenUpdating = True
    End If
End Sub

I tried changing the CodeName before deleting the worksheet:

'I replaced this line
        wsTheList.delete

'With these two lines
        ThisWorkbook.VBProject.VBComponents("wsTheList").Name = "DeleteThis"
        UserBook.Worksheets(Code2Name(UserBook, "DeleteThis")).Delete

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...

[Richard Buttrey]

Hi

As I understand it the VBA object does not include any references to VBA passwords so I don't believe you can do what you want.

Just as a matter of interest is it really vital to protect the VBE in what I'm assuming is an internal workbook.

[xladept]

Hi Renns,

Maybe you could rename it before you delete:

If MasterDate > LastUpdated Then    'If the worksheet needs to be updated...
        Application.ScreenUpdating = False
        wsTheList.Activate                  'Go to the worksheet that is being replaced.
        Set UserBook = ActiveWorkbook       'Identify the user's file.
        SheetIndex = wsTheList.Index        'Mark the location of the existing wsTheList worksheet.
        Worksheets(SheetIndex).Name = "wsThelost"
        Workbooks.Open (MasterFilePath & MasterFileName)    'Open the master file.
        Set MasterBook = ActiveWorkbook     'Identify the master file.
'Delete the old worksheet in the user's file.
        UserBook.Activate                   'Go back to the user's file.
        Application.EnableEvents = False
        Application.DisplayAlerts = False
        wsTheLost.Delete
        Application.DisplayAlerts = True
        Application.EnableEvents = True

[Artik_PL]

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.

[Renns]

Richard Buttrey:
True, I am unable to unprotect the VBProject during runtime. Unfortunately, the protection is required. It's reaching 5K lines of code and will be used by many people. So I am hoping someone knows an alternate way of acheiving my goal.

xladept:
Thanks, but the worksheet Name is not the problem, it's the CodeName that is giving me trouble. You see, even on a protected worksheet any user can change the Name by double-clicking on the tab name. This is why all my code refers to worksheets by their CodeNames.

Artik_PL:
You're right, there's no safe way to unprotect the VBProject during runtime, and I'm not interested in playing with the SendKeys method. As for just copying the data from one sheet to the other... that's something I may have to resort to, although it doesn't offer as complete of a solution. The worksheet in question has a table of ~900x3 with cell comments in each, a hidden table of ~900x17 used for filter criteria, another hidden table of ~30x3, as well as 16 checkboxes, 6 commandbuttions, 1 textbox and approx 300 lines of code. If any of the controls or the code are edited, then copying and pasting won't be enough.

-

To go back to my original post... the problem I'm having is not "How do I unprotect my VBProject during runtime?", but rather "Why does deleting the worksheet not free up its CodeName for use by the replacement worksheet?"

Thanks for the fast responses, by the way.

[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.

[Renns]

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.

[Richard Buttrey]

To go back to my original post... the problem I'm having is not "How do I unprotect my VBProject during runtime?", but rather "Why does deleting the worksheet not free up its CodeName for use by the replacement worksheet?"

Hi,

The essence of the problem is that although VBA deletes the sheet in the sense that all the sheet properties are blank, it doesn't drop the sheet object until the macro finishes. I've played around with using an On Time macro to see if it releases the sheet object but wasn't surprised to find that it doesn't. Clearly it would seem the macro needs to halt first.

The only thing I can suggest at the moment therefore is to split the macro into two. End the first one with the wsList.Delete instruction, and then get the user to click another button which would start the second macro which contains the rest of the code. Not ideal I know but at the moment I can't think of any other way given that you also want to lock the VBE with a password.

Incidentally you mentioned that you always used the sheet code name - which of course is eminently sensible given Sods law says someone will change a sheet name, but there is a line

Worksheets("The List").Activate

which doesn't.

If I can think of any other solution I'll come back.

[Renns]

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:

Private Sub WorkBook_Open()
    If (the list worksheet does not exist in the UserBook) then
        (Copy the list worksheet from the MasterBook to the UserBook)
    Else If (the list worksheet does exist in the UserBook but it needs to be updated) then
        (Delete the list worksheet from the UserBook)
        (Instruct the user to close, then re-open their file)
        'UserBook.Close True  '<--- Does not work.
    EndIf
End Sub

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.

[Richard Buttrey]

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:

Private Sub WorkBook_Open()
    If (the list worksheet does not exist in the UserBook) then
        (Copy the list worksheet from the MasterBook to the UserBook)
    Else If (the list worksheet does exist in the UserBook but it needs to be updated) then
        (Delete the list worksheet from the UserBook)
        (Instruct the user to close, then re-open their file)
        'UserBook.Close True  '<--- Does not work.
    EndIf
End Sub

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.

Hi,

Actually during my testing all that was needed was to stop the macro and then restart another one. There is no need to close the workbook as you seem to be suggesting in your Else statement.

So to clarify, my idea was

Sub ExistingMacro
'....lots of code
wsList.Delete
'...lots more code

End Sub

becomes

Sub ExistingMacro
'....lots of code
wsList.Delete

End Sub

followed by

Sub AnotherMacro
'....lots more code

End Sub

and have a button or something that the user needs to click to start the 'AnotherMacro'

[Renns]

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:

If (wsTheList does not exist) Then
    MsgBox "Hey idiot, I told you to close the file and re-open it."
    UserBook.Close True
End If

That should do it.

[Artik_PL]

Renns.
When you delete a sheet, vbProject must be recompiled. This will happen when the procedure UpdateTheList is completed. You need to run a new procedure (CopySheet) by OnTime to complete the copy sheet with the old name.
Attention! Recompile destroys all variables! If you are using global variables, you have to rebuild them before or after copying sheet.

Sub UpdateTheList()
    Dim LastUpdated As Date         'The last time the user's file was updated.
    Dim MasterDate As Date          'The last time the master file was modified.
    Dim MasterFilePath As String    'The location of the master file.
    Dim MasterFileName As String    'The filename of the master file.
    
    Dim UserBook As Workbook        'The user's copy of the file.
    Dim MasterBook As Workbook      'The master copy of the file.
    Dim SheetIndex As Integer       'The index number of the worksheet being replaced.
    
    Dim strProcedure As String
    
    MasterFilePath = ActiveWorkbook.Path & "\"
    MasterFileName = "MasterList.xlsm"
    If Not fileFolderExists(MasterFilePath & MasterFileName) Then
        MsgBox "Sorry, the file " & MasterFileName & " was not found in this location:" & Chr(13) & Chr(13) & _
               MasterFilePath & Chr(13) & Chr(13) & "Please place both UserList.xlsm and MasterList.xlsm in the same location and try again." & Chr(13) & "Stopping macro.", vbCritical, "Master File Not Found"
        Exit Sub
    End If
    
'### For the purposes of this example, set LastUpdated prior to MasterDate.
    'LastUpdated = Format(wsTheList.Range("E6").Value, "yyyy-mm-dd")    '<--- This won't even work if the CodeName has been altered.
    LastUpdated = "2012-08-01"
    MasterDate = Format(FileDateTime(MasterFilePath & MasterFileName), "yyyy-mm-dd")
    
'Compare the dates.
    If MasterDate > LastUpdated Then    'If the worksheet needs to be updated...
        Worksheets("The List").Activate
        MsgBox "The master list was updated on " & Format(MasterDate, "mmmm d, yyyy") & "." & Chr(13) & "Click OK to update your list.", vbInformation, "List Update"
        
        Set UserBook = ActiveWorkbook       'Identify the user's file.
        
        If Not WorksheetCodeNameExists(UserBook, "wsTheList") Then
            MsgBox "The worksheet's CodeName is no longer 'wsTheList', therfore your list can not be updated and the entire project no longer works." & Chr(13) & Chr(13) & _
                   "Click OK to change the CodeName from 'wsTheList1' back to 'wsTheList'." & Chr(13) & Chr(13) & _
                   "NOTE:  CHANGING A WORKSHEET'S CODENAME DURING RUNTIME WILL NOT WORK ONCE THE VBPROJECT IS PROTECTED.", vbCritical, "Program Corrupted"
            If WorksheetCodeNameExists(UserBook, "wsTheList1") Then
                Call ChangeCodeName("wsTheList1", "wsTheList")
                MsgBox "The CodeName has been changed back to 'wsTheList'." & Chr(13) & "You may now try again." & Chr(13) & Chr(13) & "Stopping macro.", vbCritical, "Reset"
            Else
                MsgBox "Apparently the worksheet's CodeName is neither 'wsTheList' or 'wsTheList1'.  You'll have to figure it out what happened on your own." & Chr(13) & Chr(13) & _
                       "Stopping macro.", vbCritical, "Fubar"
            End If
        Else
            wsTheList.Activate                  'Go to the worksheet that is being replaced.
            SheetIndex = wsTheList.Index        'Mark the location of the existing wsTheList worksheet.
            Application.ScreenUpdating = False
            Workbooks.Open (MasterFilePath & MasterFileName)    'Open the master file.
            Set MasterBook = ActiveWorkbook     'Identify the master file.
'Delete the old worksheet in the user's file.
            UserBook.Activate                   'Go back to the user's file.
            Application.EnableEvents = False
            Application.DisplayAlerts = False
    
'### Method 1:  Delete the worksheet:
            wsTheList.Delete
    
''### Method 2:  Rename the CodeName, then Delete the worksheet.  (This works, but only if the VBProject is NOT protected):
'            Call ChangeCodeName("wsTheList", "DeleteThis")       'Change the CodeName of the old worksheet to allow the replacement worksheet to have the identical CodeName (IMPORTANT).
'            UserBook.Worksheets(Code2Name(UserBook, "DeleteThis")).Delete   'Delete the worksheet containing the old verses.
            
            strProcedure = "'CopySheet """ & UserBook.Name & """,""" & _
                           MasterBook.Name & """," & SheetIndex & "'"
            Application.OnTime Now, strProcedure
        End If
    End If
End Sub


Sub CopySheet(strUsrBk As String, strMstrBk As String, iShId As Integer)
    Dim UserBook As Workbook        'The user's copy of the file.
    Dim MasterBook As Workbook      'The master copy of the file.
    Dim SheetIndex As Integer       'The index number of the worksheet being replaced.
  
    Set UserBook = Workbooks(strUsrBk)
    Set MasterBook = Workbooks(strMstrBk)
    SheetIndex = iShId
    
            Application.DisplayAlerts = True
            Application.EnableEvents = True
    'Copy the worksheet from the master file to the user's file.
            MasterBook.Worksheets(Code2Name(MasterBook, "wsTheList")).Copy _
                                        Before:=UserBook.Sheets(SheetIndex)
            MasterBook.Close False  'Close the Master file, do not save changes.
            ActiveSheet.Unprotect
            ActiveSheet.Range("E6") = Format(Now(), "mmmm d, yyyy")    'Update the "Last Updated" date.
            ActiveSheet.Protect
    'Cleanup.
            Set UserBook = Nothing      'Uninitialize
            Set MasterBook = Nothing    'Uninitialize
            Application.ScreenUpdating = True
            MsgBox "You have successfully updated your list.", vbInformation, "Success!"
End Sub

I apologize for my language. I use a translator.

Artik

[Richard Buttrey]

@Artik,

That's extremely interesting.

I thought I'd played around with the OnTime method during testing but obviously not rigorously enough, and not in quite the simple way you came up with. Well done.

There's a rep point on the way.

[Artik_PL]

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.

Well done.

Thx.

Artik

[Renns]

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.

[Renns]

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:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.DisplayCommentIndicator = xlNoIndicator 
    On Error GoTo BypassOverflowError                   
    If Target.Cells.Count = 1 Then                      
        If Not ActiveCell.Comment Is Nothing Then           
            With ActiveCell.Comment                             
                .Visible = False                                    
                .Shape.Top = ActiveCell.Offset(1, 0).Top + 2        
                .Shape.Left = ActiveCell.Left + 75                  
                .Visible = True                                     
            End With
        End If
    End If
BypassOverflowError:    
    Err.Clear
End Sub

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?

[Renns]

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!

[Artik_PL]

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

[Artik_PL]

In my Excel (2K10) works fine after copying sheet. Without re-opening.

..::Edit:
I checked it on the XL2007. It works fine.::..


Artik

[Renns]

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?

[Richard Buttrey]

Hi

Does the failure of comments to copy across also happen when you manually copy a sheet to a new workbook, i.e. right clicking the sheet name and choosing Move or Copy, Create a Copy and select 'New Book'?

How many comments are there.
MySheet.Comments.Count

[Renns]

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]

[Richard Buttrey]

Been there: got the T-shirt