VBA to find a file specific to a cell value(s), open that file, copy data.

[lbradbury]

Hello,

I have a bit of a tough one on my hands. I have a template that employees will be completing, and saving in a specific location. I have another matrix that opens, selects,copies, and pastes data from these employee files. The file names are their employeeid_year. Once all employees submit their files, managers will open the employee file, and pull data from the matrix, then build distribution charts within the employee file.

The issue I am having is the macros is written to activate the specific employee file name, but this will differ from each employee save file.
How do I write into my code below, to look for a file name specific to cell values. The employee name and year are coded to save as "123_2020". I need the macros to look for the specific employee name file, and activate this workbook and worksheet to copy data.

'Valid Macros, Do Not Remove
Sub DataPullFromMatrix()

'VBA check if file exists

Dim FilePath As String
Dim TestStr As String

'Indicate the file location below. Ensure the employee number is listed, this will change based on where the Matrix is saved

FilePath = "Z:\Employee Matrix Txt Files\Employee Matrix.xlsm"
'Error handler if file is not located

TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0
If TestStr = "" Then
MsgBox " Employee 235 File does not exist."
Else
'if file is located then opens the file
Workbooks.Open "Z:\Employee Matrix Txt Files\Employee Matrix.xlsm"
ActiveWorkbook.Save
'Copies the data table from the Matrix, onto a hidden locked sheet

Windows("123_2020.xlsm").Activate

ActiveWindow.SmallScroll Down:=-123
ActiveWindow.SmallScroll ToRight:=-21
ActiveWindow.SmallScroll Down:=-75
Range("A5:AG253").Select
Selection.ClearContents

Windows("Employee Matrix.xlsm").Activate
Sheets("Total").Select
Range("C52:AH302").Select
Application.CutCopyMode = False
Selection.Copy
Windows("123_2020.xlsm").Activate
Sheets("DataHelperSheet").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Workbooks("Employee Matrix.xlsm").Close SaveChanges:=True

'Ends use of Employee Matrix Data
End If

End Sub

 

[JLGWhiz]

You could make a list of the employee IDs in a columhn and then loop through the range in that column to open each employee's file one by one. For example, if you list their ID is column F in range F2:10 Then you could use a variable to create the file name like:

Dim fName As String, c As Range
    For Each c In Range(("F2:F10")
        fName = c.Value & "-" & Year(Date) & ".xlsx"
       Workbooks.Open fName
        'Do stuff here
        ActiveWorkbook.Close '(True, False)
    Next

 

[DanteAmor]

I'm sorry I do not understand.
You can comment the following:
In your macro you have 2 files:
- "Employee Matrix.xlsm"
- "123_2020.xlsm"
1. Which of them is the fixed name and what is the variable?
2. The variable file name where will I get it from? Do you comment that it is in a cell, that cell in which book it is, on which sheet and in which cell?
3. The range to copy is always the same?
4. The range to hit is always the same?

 

[lbradbury]

Thank you, I was able to figure this one out.