We have two List A (containing info employee information) List B (containing what projects the employees are assigned to). The lists are in two different workbooks (WB A & WB B). WB A is protected by password that only Person A has. Person A can make changes to listA, Person B can only change List B (not the employee data but just the projects they are assigned to.
We need a VBA that displays the data from list A to B. But the problem is that the folder location of WB A changes. List B should get a notification but still be able to "relink" the lists. Please note that Person B may not know WB A's new location.
Here is the macro I have in List B
Sub GetData()
Range("A2:A50").Value = "='[ListAish.xlsx]Sheet1'!B2:B50"
Range("B2:B50").Value = "='[ListAish.xlsx]Sheet1'!D2:D50"
Range("C2:C50").Value = "='[ListAish.xlsx]Sheet1'!E2:E50"
Range("D2:D50").Value = "='[ListAish.xlsx]Sheet1'!G2:G50"
Range("E2:E50").Value = "='[ListAish.xlsx]Sheet1'!F2:F50"
End Sub
(this does the updating of the appropriate columns. Instead of putting[ListAish.xlsx]Sheet1'!F2:F50 I want to put the location of the file while it changes.
Here is the Macro I have in List A
Sub FilePath()
Range("K2").Select
Selection.Copy
Range("K3").Select
Selection.PasteSpecial Paste:=xlPasteValues
End Sub
(this is just a copy and paste special macro I recorded which runs every time i open List A)
Private Sub Workbook_Open()
FilePath
End Sub
The trouble now is to make this file path appear on List B even when List A changes location.
Bookmarks