Patrick,
I guess the easiest way would be to have a look up table of SourceWSNames
and IndexWSAfter. Then just loop through all the WS's in the WB:
Dim WS as Worksheet
For Each WS in Thisworkbook.Worksheets
WS.Move After
Workbooks("Book3").Sheets(Application.VlookUp(WS.N ame,rngDataLookUp,2,True))
Next

However, I doubt you a Sheet named "Austin April.xls", so I suspect you have
multiple workbooks open. Assuming it is the first worksheet of each WB you
wish to move:
Dim WB as Workbook
For Each WB in Workbooks
WB.Worksheets(1).Move After
Workbooks("Book3").Sheets(Application.VlookUp(WB.N ame,rngDataLookUp,2,True))
Next

Need some error trapping in case VLookUp fails.
Depending exactly what you are doing, the solution may be a combination the
above 2 routines.

NickHK

"Patrick" wrote in message
...
Hi all,
a quick question on some code I have done for a macro. I have listed the
code below and basically the code had to be manually input for each sheet
that I wanted to move and the place in the new sheet had to be entered (1)
(2) (3) etc. Is there a way to enter this code so as that it will
automatically know where to put the sheets and not have to add in the
location manually.
The idea of the sheet is to rename each worksheet with the current file
name
and then move the worksheet to another open book, the end result is all
the
books become worksheets in the one book.
Any help much appreciated.
Patrick


Sheets("Austin April.xls").Move After:=Workbooks("Book3").Sheets(1)
Windows("Burr April.xls").Activate
Sheets("Burr April.xls").Move After:=Workbooks("Book3").Sheets(2)
Windows("Davis April.xls").Activate
Sheets("Davis April.xls").Move After:=Workbooks("Book3").Sheets(3)
Windows("Divall 2 April.xls").Activate
Sheets("Divall 2 April.xls").Move After:=Workbooks("Book3").Sheets(4)
Windows("Divall April.xls").Activate
Sheets("Divall April.xls").Move After:=Workbooks("Book3").Sheets(5)
Windows("Essen April.xls").Activate
Sheets("Essen April.xls").Move After:=Workbooks("Book3").Sheets(6)
Windows("Hill April.xls").Activate
Sheets("Hill April.xls").Move After:=Workbooks("Book3").Sheets(7)
Windows("Jackson April.xls").Activate
Sheets("Jackson April.xls").Move After:=Workbooks("Book3").Sheets(8)
Windows("Johnson April.xls").Activate
Sheets("Johnson April.xls").Move After:=Workbooks("Book3").Sheets(9)
Windows("Luna April.xls").Activate
Sheets("Luna April.xls").Move After:=Workbooks("Book3").Sheets(10)
Windows("McShane April.xls").Activate
Sheets("McShane April.xls").Move After:=Workbooks("Book3").Sheets(11)
Windows("Morrison April.xls").Activate
Sheets("Morrison April.xls").Move After:=Workbooks("Book3").Sheets(12)
Windows("Munguia April.xls").Activate
Sheets("Munguia April.xls").Move After:=Workbooks("Book3").Sheets(13)
Windows("Sauter April.xls").Activate
Sheets("Sauter April.xls").Move After:=Workbooks("Book3").Sheets(14)
Windows("Spencer April.xls").Activate
Sheets("Spencer April.xls").Move After:=Workbooks("Book3").Sheets(15)
Windows("Tran April.xls").Activate
Sheets("Tran April.xls").Move After:=Workbooks("Book3").Sheets(16)
Windows("Vargas April.xls").Activate
Sheets("Vargas April.xls").Move After:=Workbooks("Book3").Sheets(17)
Windows("Batchan April.xls").Activate
ActiveSheet.Name = ActiveWorkbook.Name
Sheets("Batchan April.xls").Move After:=Workbooks("Book3").Sheets(18)
Windows("Bobby April.xls").Activate
Sheets("Bobby April.xls").Move After:=Workbooks("Book3").Sheets(19)
Windows("Desboine April.xls").Activate
Sheets("Desboine April.xls").Move After:=Workbooks("Book3").Sheets(20)
Windows("Dillenburg April.xls").Activate
Sheets("Dillenburg April.xls").Move
After:=Workbooks("Book3").Sheets(21)
Windows("Killenger April.xls").Activate
Sheets("Killenger April.xls").Move
After:=Workbooks("Book3").Sheets(22)
Windows("Lawrence April.xls").Activate
Sheets("Lawrence April.xls").Move After:=Workbooks("Book3").Sheets(23)
Windows("Lopez April.xls").Activate
Sheets("Lopez April.xls").Move After:=Workbooks("Book3").Sheets(24)
Windows("Maloch April.xls").Activate
Sheets("Maloch April.xls").Move After:=Workbooks("Book3").Sheets(25)
Windows("Ochoa April.xls").Activate
Sheets("Ochoa April.xls").Move After:=Workbooks("Book3").Sheets(26)
Windows("Rogers April.xls").Activate
Sheets("Rogers April.xls").Move After:=Workbooks("Book3").Sheets(27)
Windows("Thomas April.xls").Activate
Sheets("Thomas April.xls").Move After:=Workbooks("Book3").Sheets(28)