With my workbook names all being people's names, how can I add a variable at
the end that is going to move it to the next file?
P jave tried <a but obviously my understanding is incorrect as to what can
be selected here?
An example of my file names would be jone.xls , smith.xls, brown.xls etc.
Help !!!
Patrick


"Corey" wrote in message
...
Need to change this to suit your needs.

--
Regards

Corey
"Patrick" wrote in message
...
What does the '<<<<< stand for?
Patrick


"NickHK" wrote in message
...
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