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

 

[Mentor82]

One more think. Do you have this compile error only in the test minion workbook where you pasted in or in the other minion workbooks too?

 

[XrayLemi]

Hi Sebastian,
I have not been at work this week so sorry for the delayed response. I cannot upload the files because of security. I cannot even use XL2BB due to the security here. I'll make a copy of the files and run them with no passwords to see if I get the highlighted text you need.
Jim

 

[XrayLemi]

I also didn't see your last post. Only the minion files give me errors. The master list file only has a list of names on sheet one. There is only one sheet in the master file. The master only has the following codes.

This is in the workbook to protect 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
End Sub

This is in Sheet 1 to keep the list in Alphabetical order.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 1 Then

 Dim LastRow As Long
 LastRow = Cells(Rows.Count, 1).End(xlUp).Row
 Range("a2:a" & LastRow).Sort key1:=Range("A2:A" & LastRow), order1:=xlAscending, Header:=xlNo
 
 End If
  
End Sub

That is the only code in the master file. The rest of the code is in the other workbooks. Those are the ones the rest of the crew will use. The master will only be accessed by five or six people.

 

[XrayLemi]

Okay,
I saved a new copy of the file. I removed all the protections / passwords from the VBA code and each of the sheets. I removed the protection from the VBA code. I removed all my original code from each sheet. I only left the original code you gave me for the This Workbook module. This is the only code in the "this Workbook" section.

Private Sub Workbook_Open()
   UpdateDataFromMasterFile
End Sub

I also only left the new code you gave me for sheet 1. When I open the File I get the compile error in image "Compile error 2. When I hit OK, This is what Highlights (image compile error 3).

After This, I will Open a new WB from scratch and re-do the whole workbook. This is because you say you do not get the errors. Maybe I have a glitch in the file?

Thanks again,
Jim

 

[XrayLemi]

Hi Sebastian.
I just made a completely new workbook. I figured maybe I had a corrupt file because yours worked. However, I got the same errors as I did in the post I made just before this one. I only added the code in This workbook module and the revised code you did for sheet 1. No other code is in this file.
Jim

 

[XrayLemi]

Ok, This is the second try as my last post seems to have vanished. When I removed the "UpdateDataFromMasterFile" code from the "This Workbook" module, and only use the Update button, it works PERFECT! It even works on multiple sheets. The list updates, the master file closes, and there is no trace or prompt to save the master file. It just will not work on open.

 

[XrayLemi]

Hello Sebastian!
I FINALLY GOT IT!! I stumbled onto some information and found out I needed to add a module. the newer code you gave me on May 16 goes into a module, not to each sheet. Now the link in the "this workbook" works perfectly. so do the update buttons on each sheet. EVERYTHING works perfectly!
Maybe i'm actually learning something here.
Sorry I was such a pain.
Thank you so much!
Jim Lemieux