Ok,
I have gotten the code below to work BUT it loops on the same file instead
of moving on to the next file. My goal is to open all the files in a
particular directory, renmae the worksheet with the file name and then move
that worksheet to the workbook which the macro is running from i.e. in this
case book2.
Any fix for the incorrect looping?
Only learning here and so need a lot of help
Patrick


Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "F:\Work Stuff 2\Work Stuff\Promotion Report"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
ActiveSheet.Name = ActiveWorkbook.Name
ActiveSheet.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
Loop
End Sub

Patrick,
You need to get the next available file to work on:
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
ActiveSheet.Name = ActiveWorkbook.Name
ActiveSheet.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
TheFile =Dir() '<<<
Loop

Also you have the WB reference so why not use it to make it more clear which
workbook/sheet you are working on.
Also, how do you know what the ActiveSheet of the open workbook is ? It will
depend which was active when the workbook was last closed.
It would better to close the WB if you are finished to avoid having a lot of
open workbooks.

Do While TheFile < ""
Set WB = Workbooks.Open(MyPath & "\" & TheFile)
With WB.WorkSheets(1)
.Name = WB.Name
.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
End With
WB.Close True/False
TheFile =Dir() '<<<
Loop


"Patrick" wrote in message
...
Ok,
I have gotten the code below to work BUT it loops on the same file
instead
of moving on to the next file. My goal is to open all the files in a
particular directory, renmae the worksheet with the file name and then
move
that worksheet to the workbook which the macro is running from i.e. in
this
case book2.
Any fix for the incorrect looping?
Only learning here and so need a lot of help
Patrick


Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "F:\Work Stuff 2\Work Stuff\Promotion Report"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
ActiveSheet.Name = ActiveWorkbook.Name
ActiveSheet.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
Loop
End Sub

The Book2 reference is where the macro is running from and the book I want
the opened sheets to be moved to once they are renamed.
There is only one sheet in each workbook which is being opened and so once
that sheet is moved to the current book2 worksheet the original file would
normally close. I have done this already with a code which specifies file
names and sheet names BUT I now would like if I could make it more wild card
based due to the file names in the folder being changed and this would mean
changing the code each time (which might be every week) and any new files
might also get missed.
Patrick


"NickHK" wrote in message
...
Patrick,
You need to get the next available file to work on:
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
ActiveSheet.Name = ActiveWorkbook.Name
ActiveSheet.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
TheFile =Dir() '<<<
Loop

Also you have the WB reference so why not use it to make it more clear
which
workbook/sheet you are working on.
Also, how do you know what the ActiveSheet of the open workbook is ? It
will
depend which was active when the workbook was last closed.
It would better to close the WB if you are finished to avoid having a lot
of
open workbooks.

Do While TheFile < ""
Set WB = Workbooks.Open(MyPath & "\" & TheFile)
With WB.WorkSheets(1)
.Name = WB.Name
.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
End With
WB.Close True/False
TheFile =Dir() '<<<
Loop


"Patrick" wrote in message
...
Ok,
I have gotten the code below to work BUT it loops on the same file
instead
of moving on to the next file. My goal is to open all the files in a
particular directory, renmae the worksheet with the file name and then
move
that worksheet to the workbook which the macro is running from i.e. in
this
case book2.
Any fix for the incorrect looping?
Only learning here and so need a lot of help
Patrick


Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "F:\Work Stuff 2\Work Stuff\Promotion Report"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
ActiveSheet.Name = ActiveWorkbook.Name
ActiveSheet.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
Loop
End Sub

Patrick,
You are already working on all .xls in that folder.
Or you mean you want to narrow the criteria of workbooks that are opened.
You can use a pattern in the initial Dir call e.g.
TheFile = Dir("Some Pattern*InFileName*.xls")

NickHK

"Patrick" wrote in message
...
The Book2 reference is where the macro is running from and the book I want
the opened sheets to be moved to once they are renamed.
There is only one sheet in each workbook which is being opened and so once
that sheet is moved to the current book2 worksheet the original file would
normally close. I have done this already with a code which specifies file
names and sheet names BUT I now would like if I could make it more wild
card
based due to the file names in the folder being changed and this would
mean
changing the code each time (which might be every week) and any new files
might also get missed.
Patrick


"NickHK" wrote in message
...
Patrick,
You need to get the next available file to work on:
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
ActiveSheet.Name = ActiveWorkbook.Name
ActiveSheet.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
TheFile =Dir() '<<<
Loop

Also you have the WB reference so why not use it to make it more clear
which
workbook/sheet you are working on.
Also, how do you know what the ActiveSheet of the open workbook is ? It
will
depend which was active when the workbook was last closed.
It would better to close the WB if you are finished to avoid having a
lot
of
open workbooks.

Do While TheFile < ""
Set WB = Workbooks.Open(MyPath & "\" & TheFile)
With WB.WorkSheets(1)
.Name = WB.Name
.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
End With
WB.Close True/False
TheFile =Dir() '<<<
Loop


"Patrick" wrote in message
...
Ok,
I have gotten the code below to work BUT it loops on the same file
instead
of moving on to the next file. My goal is to open all the files in a
particular directory, renmae the worksheet with the file name and then
move
that worksheet to the workbook which the macro is running from i.e. in
this
case book2.
Any fix for the incorrect looping?
Only learning here and so need a lot of help
Patrick


Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "F:\Work Stuff 2\Work Stuff\Promotion Report"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
ActiveSheet.Name = ActiveWorkbook.Name
ActiveSheet.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
Loop
End Sub

Yes I know, but what is currently happening is that the macro loops on the
first file and keeps reopning it and moving it to the book2 and so it ends
up with endless sheets of the same workboo.
Patrick :)


"NickHK" wrote in message
...
Patrick,
You are already working on all .xls in that folder.
Or you mean you want to narrow the criteria of workbooks that are opened.
You can use a pattern in the initial Dir call e.g.
TheFile = Dir("Some Pattern*InFileName*.xls")

NickHK

"Patrick" wrote in message
...
The Book2 reference is where the macro is running from and the book I
want
the opened sheets to be moved to once they are renamed.
There is only one sheet in each workbook which is being opened and so
once
that sheet is moved to the current book2 worksheet the original file
would
normally close. I have done this already with a code which specifies file
names and sheet names BUT I now would like if I could make it more wild
card
based due to the file names in the folder being changed and this would
mean
changing the code each time (which might be every week) and any new files
might also get missed.
Patrick


"NickHK" wrote in message
...
Patrick,
You need to get the next available file to work on:
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
ActiveSheet.Name = ActiveWorkbook.Name
ActiveSheet.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
TheFile =Dir() '<<<
Loop

Also you have the WB reference so why not use it to make it more clear
which
workbook/sheet you are working on.
Also, how do you know what the ActiveSheet of the open workbook is ? It
will
depend which was active when the workbook was last closed.
It would better to close the WB if you are finished to avoid having a
lot
of
open workbooks.

Do While TheFile < ""
Set WB = Workbooks.Open(MyPath & "\" & TheFile)
With WB.WorkSheets(1)
.Name = WB.Name
.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
End With
WB.Close True/False
TheFile =Dir() '<<<
Loop


"Patrick" wrote in message
...
Ok,
I have gotten the code below to work BUT it loops on the same file
instead
of moving on to the next file. My goal is to open all the files in a
particular directory, renmae the worksheet with the file name and then
move
that worksheet to the workbook which the macro is running from i.e. in
this
case book2.
Any fix for the incorrect looping?
Only learning here and so need a lot of help
Patrick


Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "F:\Work Stuff 2\Work Stuff\Promotion Report"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
ActiveSheet.Name = ActiveWorkbook.Name
ActiveSheet.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
Loop
End Sub

So you did not add the extra I marked with '<<<<< in my previous post ?
Without that, yes you are going to reopen the same file. With that, you will
work through the files in the folder.

NickHK

"Patrick" wrote in message
...
Yes I know, but what is currently happening is that the macro loops on the
first file and keeps reopning it and moving it to the book2 and so it ends
up with endless sheets of the same workboo.
Patrick :)


"NickHK" wrote in message
...
Patrick,
You are already working on all .xls in that folder.
Or you mean you want to narrow the criteria of workbooks that are
opened.
You can use a pattern in the initial Dir call e.g.
TheFile = Dir("Some Pattern*InFileName*.xls")

NickHK

"Patrick" wrote in message
...
The Book2 reference is where the macro is running from and the book I
want
the opened sheets to be moved to once they are renamed.
There is only one sheet in each workbook which is being opened and so
once
that sheet is moved to the current book2 worksheet the original file
would
normally close. I have done this already with a code which specifies
file
names and sheet names BUT I now would like if I could make it more wild
card
based due to the file names in the folder being changed and this would
mean
changing the code each time (which might be every week) and any new
files
might also get missed.
Patrick


"NickHK" wrote in message
...
Patrick,
You need to get the next available file to work on:
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
ActiveSheet.Name = ActiveWorkbook.Name
ActiveSheet.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
TheFile =Dir() '<<<
Loop

Also you have the WB reference so why not use it to make it more
clear
which
workbook/sheet you are working on.
Also, how do you know what the ActiveSheet of the open workbook is ?
It
will
depend which was active when the workbook was last closed.
It would better to close the WB if you are finished to avoid having a
lot
of
open workbooks.

Do While TheFile < ""
Set WB = Workbooks.Open(MyPath & "\" & TheFile)
With WB.WorkSheets(1)
.Name = WB.Name
.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
End With
WB.Close True/False
TheFile =Dir() '<<<
Loop


"Patrick" wrote in message
...
Ok,
I have gotten the code below to work BUT it loops on the same file
instead
of moving on to the next file. My goal is to open all the files in a
particular directory, renmae the worksheet with the file name and
then
move
that worksheet to the workbook which the macro is running from i.e.
in
this
case book2.
Any fix for the incorrect looping?
Only learning here and so need a lot of help
Patrick


Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "F:\Work Stuff 2\Work Stuff\Promotion Report"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
ActiveSheet.Name = ActiveWorkbook.Name
ActiveSheet.Move After:=Workbooks("Book2").Sheets(Sheets.Count)
Loop
End Sub