VBscript to merge multiple excel files into a new excel file : I know the question has been asked so many times already, and I have tried to use Google to search the interest but failed to find the correct VBscript. ( Trust me, I am not a taker). 

Anyway, the idea is to run a VBscript to merge all excel files (CAD,GBP,JPY,USD) to a new excel file ( tab shows name "CAD","GBP", "JPY","USD") in the current folder. I have wrote the following script to merge excel files, but it does not even work. 

Please kindly help me on it. Thanks for the time.

Sub CombineWorkbooks()
     
    Dim Path            As String
    Dim FileName        As String
    Dim Wkb             As Workbook
    Dim WS              As Worksheet
     
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Path = "X:\PATH\TO\EXCEL\FILES" 
    FileName = Dir(Path & "\*.xls", vbNormal)
    Do Until FileName = ""
        Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
        For Each WS In Wkb.Worksheets
            WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Next WS
        Wkb.Close False
        FileName = Dir()
    Loop
    Application.EnableEvents = True
    Application.ScreenUpdating = True
     
End Sub