Move Files from Folder to Folder
 

***Sorry I'm reposting because I placed it in the wrong category****

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
combined 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.Cou nt)
Next WS
Wkb2.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

Move Files from Folder to Folder
 

You can use the Name statement to name your C:\New folder to
C:\Old, then use Midair to recreate the C:\New folder. Eg.,

Name "C:\New" As "C:\Old"
MkDir "C:\New"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"THE_RAMONES" wrote in
message
...
***Sorry I'm reposting because I placed it in the wrong
category****

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
combined 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.Cou nt)
Next WS
Wkb2.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

Move Files from Folder to Folder
 

My old folder should be a cumulative total of all old files. So, if I
understand this correctly I will be renaming the new folder to old however I
would overwrite all the old files. If I'm totally messing this thought up
please let me know. Thanks

Ramon


"Chip Pearson" wrote:

You can use the Name statement to name your C:\New folder to
C:\Old, then use Midair to recreate the C:\New folder. Eg.,

Name "C:\New" As "C:\Old"
MkDir "C:\New"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"THE_RAMONES" wrote in
message
...
***Sorry I'm reposting because I placed it in the wrong
category****

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
combined 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.Cou nt)
Next WS
Wkb2.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True