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)