Update a worksheet based on other worksheets

Doe,John.xlsxSTAFF LIST.xlsm

I need to update the Staff List based on the info in the workbooks with an employee for a file name. In the staff list, I want to end up with the employee name in column A followed by the info in cells K5, L5, M5, and N5 from the workbook with employee name for the file name. I want to do this for every employee workbook in the folder. Using the VBA macro in the attached Staff List, I end up with too many columns in my results. Please correct my VBA macro to give me only desired results.

Hi gibson2503

Look at this

Option Explicit

Sub ImpData()
    Dim Wb As Workbook
    Dim sFile As String
    Dim sPath As String
    Dim sName As String
    Dim i As Integer
    Dim j As Integer

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Range("A1") = "Staff name"

    sPath = "S:\PAYROLL\Quimby and Adams\Tracking Spreadsheets\PP 13-12 TO 13-14\HYBRID F\ACTIVE\"
    sFile = Dir(sPath & "*.xlsx")
    Do While sFile <> ""
        Set Wb = Workbooks.Open(sPath & sFile)
        sName = Mid(sFile, 1, Len(sFile) - 5)
        Sheets(1).Activate
        Range("K5:N5").Copy
        Windows("STAFF LIST.xlsm").Activate
        Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        Wb.Activate
        Windows("STAFF LIST.xlsm").Activate
        
        '##### This line of Code is pasting the same Data a second time
        Range("F" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        '#################################
        
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = sName
        Wb.Close SaveChanges:=False
        sFile = Dir
    Loop
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True


End Sub

Hi gibson2503

You're welcome...glad I could help.

If this resolves your issue please mark your Thread as SOLVED.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save