Merging Multiple Workbooks into Master Wbook

[dellzy]

Hi Excel Experts,

I found this awesome vba code to allow me copy every wsheet from multiple workbooks into one Master Wbook, then move the file to folder named "Done" in the same Master Wbook path. However, when it finds that the file it moves already exist in folder "Done", the macro will stop and point to the Master Workbook.

I need it to be able to rename the file (increment by 1 each time find existing) in parent path (maintaining the same file extension as original) then move to the "Done" folder.

My edited code successfully move the file if found the file not exist in "Done" folder, but still not working if the file has existed. Please help to correct my code:-

Do While FileName <> ""
  
Workbooks.Open FileName:=myPath & FileName, local:=True, ReadOnly:=True

For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy after:=ThisWorkbook.Sheets("Macros")
Next Sheet

Workbooks(FileName).Close savechanges:=False
      If FSO.FileExists(ToDir & FileName) = False Then
      MsgBox "file doesn't exist"
      FSO.MoveFile Source:=myPath & FileName, Destination:=ToDir
    Else
      MsgBox "File exist"
      NewName = FileName & "(" & j & ")" '<== versioning to differentiate the file
      FSO.MoveFile FileName, NewName '<== replace the old file
      FSO.MoveFile Source:=myPath & NewName, Destination:=ToDir '<== move to the destination folderEnd If

FileName = Dir()
Loop

Thanks in advance.
DZ