building new workbook from sheets in different workbooks
 

Hi,
can anyone help, please?
I've 5 workbooks, each containing the sheets A-Z (Customernames).
Now I need a macro that goes into each of the workbooks, selects all
sheets with name "A" and saves it as new file "A", then goes back and
continues with sheet "B" and so on.
Anyone an idea?

building new workbook from sheets in different workbooks
 

create a file that holds the customernames in a list (A2:A15),
highlight the first customer, open all workbooks holding customer
information

Sub Create_CostCentrePack()

Application.ScreenUpdating = False
Dim CostCentre As String

Do While True

ActiveCell.Select
CostCentre = ActiveCell.FormulaR1C1
'MsgBox CostCentre
Workbooks.Add
ChDir "H:\Test" (to be replaced by final path)
ActiveWorkbook.SaveAs Filename:="H:\Test\" & CostCentre & ".xls", _
FileFormat:=xlNormal, CreateBackup:=False

Windows("file which contains sheets to be copied").Activate
Sheets(CostCentre).Select
Sheets(CostCentre).Copy Befo=Workbooks(CostCentre).Sheets(1)

Windows("second file which contains sheets to be
copied").Activate
Sheets(CostCentre).Select
Sheets(CostCentre).Copy Befo=Workbooks(CostCentre).Sheets(2)

(....and so on)

ActiveWorkbook.Save

ActiveWindow.Close
Windows("Macro.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Loop

End Sub