Move File

[Guest]

I have a macro that goes into a folder, grabs all excel documents and
combines into one workbook. I want to move that particular file after its
combine to the master file. Basically, I have two folders

1. New
2 Old

I use the code below. Once I use the file I would like to move it to the Old
Folder. So, the next day it will be ready for the next group of files. Thanks

Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "c:\new" ''Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb2 = Workbooks.Open(FileName:=Path & "\" & FileName)
For Each WS In Wkb2.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb2.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

 

[Guest]

You can insert a code like this at the beginning:
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
PathOld = "c:\old\" 'Change as needed

And then after you close the file:
Wkb2.Close False
fso.MoveFile Path & "\" & FileName, PathOld
FileName = Dir()

Be sure to have backup of your files before testing the code, just in case.

Hope this helps,
Miguel.

 

[Guest]

I think we may be close... However I get an error saying file already exist.
However there are no files in the folder.. Any thoughts? Thanks

 

[Guest]

I have tried the code, and I cannot get the same error. This is the code I
used:
Sub test()
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
PathOld = "c:\old\" 'Change as needed
Path = "c:\new" ''Change as needed
Filename = Dir(Path & "\*.xls", vbNormal)
Do Until Filename = ""
Set Wkb2 = Workbooks.Open(Filename:=Path & "\" & Filename)
For Each WS In Wkb2.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb2.Close False
fso.MoveFile Path & "\" & Filename, PathOld
Filename = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Miguel.