If I understand you correctly, you want to update the name list on StaffInfo.xls Sheet2 whenever the names on DBOutput.xls Sheet1 may change.
Place both StaffInfo.xls and DBOutput.xls in same location.
Alt + C >> Copy macro below
Open StaffInfo.xls
Alt + F11 >> Opens VBE
Alt + I, M >> Inserts new module
Alt + V >> Paste macor in new module
Alt + Q >> Close VBE & return to Excel
Where do I paste the code
Sub CopyNames()
Dim _
MyPath As String, _
MyFile As String, _
FName As String, _
LR As Long, _
LR1 As Long
With Sheets("Sheet2")
.Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).ClearContents
End With
MyPath = ActiveWorkbook.Path & "\"
MyFile = "DBOutput.xls"
FName = Dir(MyPath & MyFile)
If FName = "" Then
MsgBox "The file " & MyFile & " was not found", , "File Doesn't Exist"
Exit Sub
End If
Workbooks.Open Filename:=MyPath & FName
With Workbooks(FName).Sheets("Sheet1")
LR = .Range("A" & Rows.Count).End(xlUp).Row
.Range(.Cells(2, 1), .Cells(LR, 1)).Copy _
Workbooks("StaffInfo.xls").Sheets("Sheet2").Range("A1")
End With
Workbooks(FName).Close
Exit Sub
End Sub
in the name name manger update "names" with
=Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH(REPT("Z",255),Sheet2!$A:$A))
This will make the list dynamic as the list grows or shrinks.
Bookmarks