Copy Used Range Other Workbooks Other Dir n Paste Master WB Sheet1

[Logit]

The following macro is straight from the MS website ... doesn't work ... surprise ! My frustration level after several days is indescribable. Please help.

There are three workbooks sitting in a folder named TEST that is sitting on my desktop. The master workbook named Master is sitting on the desktop adjacent to this folder.
The three workbooks are named Book1, Book2, Book3.

The macro should open each workbook, one at a time, copy the used range on sheet named Staffing-Processes and paste that in the Master workbook / Sheet1 first blank row column A.

The macro runs flawlessly here - no errors ... but it doesn't do anything !

Sub CommandButton1_Click()    ' Change folder path as needed, keep the trailing backslash
    Const sFolder = "C:\Users\gagli\OneDrive\Desktop\Test"
    Dim sFile As String
    Dim wshT As Worksheet
    Dim t As Long
    Dim wbkS As Workbook
    Dim wshS As Worksheet
    Dim s As Long
    Dim m As Long
    Application.ScreenUpdating = False
    ' Target sheet
    Set wshT = ThisWorkbook.Worksheets("Sheet1") ' or use ActiveSheet
    ' First available target row
    t = wshT.Range("A" & wshT.Rows.Count).End(xlUp).Row + 2
    ' Get first Excel filename in the folder
    sFile = Dir(sFolder & "*.xls*")
    ' Loop through the files
    Do While sFile <> ""
        ' Open source workbook
        Set wbkS = Workbooks.Open(sFolder & sFile)
        ' Refer to the first sheet
        Set wshS = wbkS.Worksheets(1)
        ' Get the last used row
        m = wshS.Range("A" & wshS.Rows.Count).End(xlUp).Row
        ' Copy range
        wshS.Range("A2:Z" & m).Copy Destination:=wshT.Range("A" & t)
        ' Increment target row
        t = t + m - 1
        ' Turn off clipboard
        Application.CutCopyMode = False
        ' Close source workbook
        wbkS.Close SaveChanges:=False
        ' Get next filename
        sFile = Dir
    Loop
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub

[Paul_Hossler]

Try this

Also renamed some variables so I could follow

Option Explicit


Sub CommandButton1_Click()
    ' Change folder path as needed, keep the trailing backslash
    Const sFolder = "C:\Users\Daddy\Downloads\"     '   back slash !!! <<<<<<<<<<<<<<<
    
    Dim sFile As String
    Dim mstWS As Worksheet
    Dim srcWB As Workbook
    Dim srcWS As Worksheet
    Dim t As Long, s As Long, srcNextRow As Long
    
    Application.ScreenUpdating = False
    
    ' Target sheet
    Set mstWS = ThisWorkbook.Worksheets("Sheet1") ' or use ActiveSheet
    
    ' First available target row
    t = mstWS.Range("A" & mstWS.Rows.Count).End(xlUp).Row
    
    ' Get first Excel filename in the folder
    sFile = Dir(sFolder & "*.xls*")
    
    ' Loop through the files
    Do While sFile <> ""
        
        ' Open source workbook
        Set srcWB = Workbooks.Open(sFolder & sFile)
        
        If Not srcWB Is ThisWorkbook Then       '   don't use this WB <<<<<<<<<<<<<<<<
        
            ' Refer to the first sheet
            Set srcWS = srcWB.Worksheets(1)
            
            ' Get the last used row
            srcNextRow = srcWS.Range("A" & srcWS.Rows.Count).End(xlUp).Row + 1
            
            ' Copy range
            srcWS.Range("A2:Z" & srcNextRow).Copy Destination:=mstWS.Range("A" & t)
            
            ' Increment target row
            t = t + srcNextRow - 2       '   <<<<<<<<<<<<<<<<< is this what you wanted
            
            ' Turn off clipboard
            Application.CutCopyMode = False
            
            ' Close source workbook
            srcWB.Close SaveChanges:=False
        End If
        
        
        ' Get next filename
        sFile = Dir
    Loop
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub

[Logit]

Thank you for your response. Works brilliantly ! Have a great day !!!