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

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

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


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



"THE_RAMONES" <[email protected]> wrote in
message
news:[email protected]...
> ***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.Count)
> Next WS
> Wkb2.Close False
> FileName = Dir()
> Loop
> Application.EnableEvents = True
> Application.ScreenUpdating = True

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. E>g.,
>
> Name "C:\New" As "C:\Old"
> MkDir "C:\New"
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "THE_RAMONES" <[email protected]> wrote in
> message
> news:[email protected]...
> > ***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.Count)
> > Next WS
> > Wkb2.Close False
> > FileName = Dir()
> > Loop
> > Application.EnableEvents = True
> > Application.ScreenUpdating = True
>
>
>