Copy data from a closed master WB to different WBs when the different WBs are initially opened. Way over my head !

[XrayLemi]

Good morning everyone. I am in way over my head. and looking for help.....Again. All the other posts on this forum I have seen seem to go the opposite direction. I need a way to copy / update information listed on a master workbook to many other WBs. The master WB will just have regular cell entries (names) All the subsequent WBs (I'll call them minions) will have a sheet that will contain entries identical to the master. So if The master contains the entries in cells A1-A3 **** Jane Spot on sheet 1, the minion WBs will have the same entries in the same cells on the same sheet. I will use the info on sheet 1 to create drop down lists on the minions. I also need to be sure that when new entries are created on the master, the next time the minion is opened it will check and add entries if the master is updated.

From what information I have found, the master must be opened to update the minions. If this is true, The master must be opened, The info updated, and the master closed without the user seeing and / or having access to the master.

There is also coding already in use.

This code is in the "this workbook" section to protect the sheets. I will post the sheet code next. I cannot get both codes on the came post.

Private Sub Workbook_Open()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ' UserInterfaceOnly:=True allows code to change data.
    ws.Protect "green", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True
  Next ws
End Sub

 

[XrayLemi]

this code is used on all sheets in the WB.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, c As Range
  Set r = Range("G6:G5000")
  Set r = Intersect(Target, r)
  If r Is Nothing Then Exit Sub
  
  Application.EnableEvents = False
  
  For Each c In r
    Select Case True
      Case 7 = c.Column 'G
        If c.Value = "" Then
          Cells(c.Row, "H").Value = ""
          Cells(c.Row, "H").Locked = True
          Else
          Cells(c.Row, "H").Locked = False
        End If
      Case Else
    End Select
  Next c
  
  Application.EnableEvents = True
End Sub

I posted this in case any new code will affect the old. As always, I know I ask allot, and REALLY appreciate what the members here can do.
Thank you SO much in advance
Jim Lemieux

 

[Mentor82]

Good morning everyone. I am in way over my head. and looking for help.....Again. All the other posts on this forum I have seen seem to go the opposite direction. I need a way to copy / update information listed on a master workbook to many other WBs. The master WB will just have regular cell entries (names) All the subsequent WBs (I'll call them minions) will have a sheet that will contain entries identical to the master. So if The master contains the entries in cells A1-A3 **** Jane Spot on sheet 1, the minion WBs will have the same entries in the same cells on the same sheet. I will use the info on sheet 1 to create drop down lists on the minions. I also need to be sure that when new entries are created on the master, the next time the minion is opened it will check and add entries if the master is updated.

From what information I have found, the master must be opened to update the minions. If this is true, The master must be opened, The info updated, and the master closed without the user seeing and / or having access to the master.

There is also coding already in use.

This code is in the "this workbook" section to protect the sheets. I will post the sheet code next. I cannot get both codes on the came post.

Private Sub Workbook_Open()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ' UserInterfaceOnly:=True allows code to change data.
    ws.Protect "green", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True
  Next ws
End Sub

Hi,
I need more details to give you a hand.
1. You want to pull the new entries only from main workbook to the minors or update the existing ones if changed too?
2. You want to pull data on demand or when ocenimy the minor workbook?
3. Which column and row your data, which you want to have updated, starts from?
By the way, the case statement that you used in above code is not necessary because you loop c object within the range which is set to G column only so it no need to check if the c is in column G. You can get a rid of this case statement.

 

[XrayLemi]

Hi Mentor,
All the entries will be in sheet 1 column A from 2 to 5000 ish. Entries will be ongoing. These will match the same sheet and column in any WB connected to the master. I hadn't Thought of changes to existing entries, But if a name is spelled wrong I will need to change that as well. Great catch! That's why I love you guys! The data will only need to update when you first open the file. Then the master can be closed. Changes will not happen every few minutes to the master only once or twice a day or fewer. I guess if we got fancy, a button could be added on each sheet that says UPDATE and that could update the list from the master if any changes to the master were made before closing the file. I also don't know if it matters but all the cells in the master list will be locked to prevent changes. The master will be on the same drive but in a separate folder (if that helps)
The master will be connected to a number of other WBs. If two WBs are opened at the same time, one master will open normal the other as read only. I don't know if it matters if the master opens as read only or not?
As for the suggestion to change the existing code, Thank you, BUT, everything works perfectly. I make too many errors and I don't want to change it unless it gives me a performance upgrade.
Thanks for the fast reply!
Jim Lemieux

 

[Mentor82]

Hi,
Because you want user to be able to run the update using a button from the "Minion file", I decided to make a code for you which will work when you run it using the update button in the "Minion files" as well as you can add it to the workbook module on open action so when you open the Minion file the update will run automatically. The code I've made for you is based on pulling method do it's initialized from the Minion file and pulls the data from the Master workbook not another way around. In my opinio it'll suffice your needs but if you do need to push the data from the Master workbook to the Minions, I'd need to write a separate code a bit different to the one attached

Anyway, here's the code. Copy it and paste to your vba module.
Than set up this constant typing in your master file location including file extension whatever you have (xlsx, xlsm etc.)
Const wbMasterFileDir$ = "C:\Users\Sebastian\Desktop\MrExcel\Excel7\MasterFile\MasterFile.xlsx"

Sub UpdateDataFromMasterFile()
    Dim wbMaster As Workbook
    Dim wbMinion As Workbook
    Dim wsMaster As Worksheet
    Dim wsMinion As Worksheet
    Dim noRows&
    Dim i&
    Dim arrMaster()
    Dim arrMinion()
    
    On Error GoTo ErrorHandler
    
    'Change this constant to your master file full directory including file extension whatever you have (xls,xlsx, xlsm etc.)
    Const wbMasterFileDir$ = "C:\Users\Sebastian\Desktop\MrExcel\Excel7\MasterFile\MasterFile.xlsx"
    
    If Not (Len(Dir(wbMasterFileDir)) > 0) Then
        MsgBox "Provided master file directory does not exist!" & vbNewLine & _
                "Path: " & wbMasterFileDir, vbCritical, "InfoLog"
        Exit Sub
    End If
    
    Set wbMinion = ThisWorkbook
    Set wsMinion = wbMinion.Sheets(1)
    
    Set wbMaster = GetObject(wbMasterFileDir)
    Set wsMaster = wbMaster.Sheets(1)
    
    With wsMaster
        noRows = .Range("A" & Cells.Rows.Count).End(xlUp).Row
        
        If noRows = 1 Then
            MsgBox "There's no data to pull from Master File!", vbExclamation, "InfoLog"
            Exit Sub
        End If
        
        arrMaster = .Range("A2:A" & noRows)
        arrMinion = wsMinion.Range("A2:A" & noRows)
    End With
    
    For i = 1 To UBound(arrMaster, 1)
        arrMinion(i, 1) = arrMaster(i, 1)
    Next i
        
    wsMinion.Range("A2:A" & noRows) = arrMinion
            
    MsgBox "Update completed.", vbInformation, "InfoLog"
    
DataClearance:
    Set wbMinion = Nothing
    Set wsMinion = Nothing
    Set wbMaster = Nothing
    Set wbMinion = Nothing
    
    Exit Sub

ErrorHandler:
    MsgBox "Unexpected error occured!", vbCritical, "InfoLog"
    Resume DataClearance
    
End Sub

Than, add a button to your Minion files in sheet 1, name it whatever you like ex. Update, and reference it to the macro: Sub UpdateDataFromMasterFile or if you use ActiveX Command button, change its label to Update and add the following code in the sheet 1 in Minion workbooks

Private Sub CommandButton1_Click()
    UpdateDataFromMasterFile
End Sub

Then, add the following code to ThisWorkbook module in order to make the code run while opening the Minion workbook

Private Sub Workbook_Open()
    UpdateDataFromMasterFile
End Sub

Check this out and let me know if that works for you

 

[XrayLemi]

Ok Mentor,
Update file on open does not work, update with activex button works fine even on multiple pages.
Here is what happened, I copied the main code into "sheet 1". Then created an activex button. put the code in and hit update. That worked.
However, when I copied the code for "this workbook" module, and re-opened the file I got the error seen in the image "error1". Not only did the list not update, it messed up the whole workbook. None of my original coding worked, cells that were locked unlocked..etc. It gave me problems if i used the whole code.

Private Sub Workbook_Open()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ' UserInterfaceOnly:=True allows code to change data.
    ws.Protect "green", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True
  Next ws
End Sub
Private Sub Workbook_Open()
    UpdateDataFromMasterFile
End Sub

or if I just added the last part

Private Sub Workbook_Open()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ' UserInterfaceOnly:=True allows code to change data.
    ws.Protect "green", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True
  Next ws
End Sub
    UpdateDataFromMasterFile
End Sub

Once the error happened I could not even delete the code to make it work as it originally did. I opened a previous version of the file and made changes from there. I was able to successfully make an "update button" on multiple pages in a (minion) WB. As long as I put the code you gave me first, everything worked fine. I was even able to make a copy of a worksheet with a button and that worked fine as well. I won't put the code in "this workbook" for now.
There is also a strange thing that happens when you save a minion after an update. It asks you if you want to save the master file, as well as the minion even though the master file cannot be seen. Can it be made so as soon as the update is complete, the master closes without being saved? This will save me a TON of grief.
PS. I also found out that we got rid of excel 365 and are back to excel 2016 I don't know if that has anything to do with it?
As always, Thank you very much,
Jim Lemieux

 

[Mentor82]

Hi,
Thanks a lot for the reply.
First of all I'd like to mention that I don't remember you wrote you have any code on workbooks open. You cannot have two such triggers. Secondly, will you pls make tests on test workbooks only to stave off data loss or its damage in case of any error untill you sure the code works the way you want/it's been designed for.

I've changed the code a little big. I've replaced get workbook object method to open is and added screen updating in order not to see when master workbook is opening or closing. I've also added closing method for master workbook.

Here's the changed code. Paste it in to your "Test Minion workbook" and test it along with test master workbook, ok

Sub UpdateDataFromMasterFile()
    Dim wbMaster As Workbook
    Dim wbMinion As Workbook
    Dim wsMaster As Worksheet
    Dim wsMinion As Worksheet
    Dim noRows&
    Dim i&
    Dim arrMaster()
    Dim arrMinion()
    
    On Error GoTo ErrorHandler
    
    'Change this constant to your master file full directory including file extension whatever you have (xls,xlsx, xlsm etc.)
    Const wbMasterFileDir$ = "C:\Users\Sebastian\Desktop\MrExcel\Excel7\MasterFile\MasterFile.xlsx"
    
    If Not (Len(Dir(wbMasterFileDir)) > 0) Then
        MsgBox "Provided master file directory does not exist!" & vbNewLine & _
                "Path: " & wbMasterFileDir, vbCritical, "InfoLog"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    Set wbMinion = ThisWorkbook
    Set wsMinion = wbMinion.Sheets(1)
    
    Set wbMaster = Workbooks.Open(wbMasterFileDir) 'GetObject(wbMasterFileDir)
    Set wsMaster = wbMaster.Sheets(1)
    
    With wsMaster
        noRows = .Range("A" & Cells.Rows.Count).End(xlUp).Row
        
        If noRows = 1 Then
            Application.ScreenUpdating = True
            MsgBox "There's no data to pull from Master File!", vbExclamation, "InfoLog"
            Exit Sub
        End If
        
        arrMaster = .Range("A2:A" & noRows)
        
        WbMasterClose wb:=wbMaster
        
        arrMinion = wsMinion.Range("A2:A" & noRows)
    End With
    
    For i = 1 To UBound(arrMaster, 1)
        arrMinion(i, 1) = arrMaster(i, 1)
    Next i
        
    wsMinion.Range("A2:A" & noRows) = arrMinion
            
    MsgBox "Update completed.", vbInformation, "InfoLog"
    
DataClearance:
    Application.ScreenUpdating = True
    Set wbMinion = Nothing
    Set wsMinion = Nothing
    Set wbMaster = Nothing
    Set wbMinion = Nothing
    
    Exit Sub

ErrorHandler:
    MsgBox "Unexpected error occured!", vbCritical, "InfoLog"
    Resume DataClearance
    
End Sub
Private Sub WbMasterClose(wb As Workbook)
    On Error Resume Next
    Application.DisplayAlerts = False
    wb.Saved = False
    wb.Close
    Application.DisplayAlerts = True
End Sub

Are you sure you paste the call off to the method in the ThisWorkbook module on opening trigger? Try pasting the code accordingly:

Private Sub Workbook_Open()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ' UserInterfaceOnly:=True allows code to change data.
    ws.Protect "green", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True
  Next ws
  UpdateDataFromMasterFile
End Sub

Give this a try but test it in test both Minion and Master Workbooks.

 

[XrayLemi]

Hi Sebastian,
Yes, I am saving lots of copies of the test (minion) files. I can go back to other versions I have saved. I will make The final one my template for future minion files..

Okay, The only file I have open is the working file (the minion file). When the code was working at its best, after a successful update, If I close the minion file, I get two message boxes. The first one is to save the Working file "Error5" and a second one to save the Master file "error6". The message box in error6 is the thing I don't understand, and what I am trying to eliminate. That will confuse way too many of my co-workers. I think the message to save the master file comes up because the file is referenced in the minion file. Am I correct?

I still have trouble with the new code. when I save the file after changing to the new code, then re-open the file I get this... Image "compile error1" If I click OK, on the compile error boxI can use the update button on sheet 1. It updates successfully. However, all the remaining sheets in the workbook get messed up. I get errors, code does not work, cells don't unlock. etc.. I don't know if the closing part will work yet.

This is where I added your "this workbook" code and how it is copied in. image "code location" Here it is pasted just as I copied it.

Private Sub Workbook_Open()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ' UserInterfaceOnly:=True allows code to change data.
    ws.Protect "green", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True
  Next ws
  UpdateDataFromMasterFile
End Sub

Finally, I didn't think I needed to paste the code in the master file. The only thing the master contains is a list of names.

 

[Mentor82]

Hi Sebastian,
Yes, I am saving lots of copies of the test (minion) files. I can go back to other versions I have saved. I will make The final one my template for future minion files..

Okay, The only file I have open is the working file (the minion file). When the code was working at its best, after a successful update, If I close the minion file, I get two message boxes. The first one is to save the Working file "Error5" and a second one to save the Master file "error6". The message box in error6 is the thing I don't understand, and what I am trying to eliminate. That will confuse way too many of my co-workers. I think the message to save the master file comes up because the file is referenced in the minion file. Am I correct?

I still have trouble with the new code. when I save the file after changing to the new code, then re-open the file I get this... Image "compile error1" If I click OK, on the compile error boxI can use the update button on sheet 1. It updates successfully. However, all the remaining sheets in the workbook get messed up. I get errors, code does not work, cells don't unlock. etc.. I don't know if the closing part will work yet.

This is where I added your "this workbook" code and how it is copied in. image "code location" Here it is pasted just as I copied it.

Private Sub Workbook_Open()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ' UserInterfaceOnly:=True allows code to change data.
    ws.Protect "green", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True
  Next ws
  UpdateDataFromMasterFile
End Sub

Finally, I didn't think I needed to paste the code in the master file. The only thing the master contains is a list of names.

Hi,
Thanks a lot for feedback. Actually, there’s nothing special about the code I made for you and you should not get any error/compile error. I do not get any error while running the code, though. Could you upload your minion and master test files without any data, only the way it’s formatted on dropbox so I can have a look, what do you think?

 

[Mentor82]

Hi,
Can you unorotect the vba module, run workbooks open method and see which line of the code highlights?