Hello

I've been trying to write a macro to copy each worksheet within a workbook
and then save each worksheet as it's own file. However I would like to do
this dynamically (ideally to keep the macro short and easier to follow) so
that the worksheet is selected based on a cell reference and the filename it
is saved as is also based on a cell reference.
I've searched the forum and tried to use some of the solutions, to similar
queries, from there - which led me to try and use called subroutines. This
seemed like a neater soloutin but I keep getting various error messages; and
I'm not familiar enough with macros to work out what the problems are.

I've copied the "long" macro that I've developed so far and tried to comment
on what I would like to do (I hope that isn't patronising).


Sub SB001000()
'
Application.DisplayAlerts = False
' This is the workbook containing the worksheets to be copied and saved
Workbooks.Open Filename:= _
"H:\Fin Management\Education, etc\Education 2009-10\School
Reports\Budget Reports\Current month\School Budget Reports 08-02-10.xls"

' Each worksheet has a similar name (e.g. Output (001000); Output (001001)
etc) Ideally dynamically obtain these names based on a range of cell
reference - e.g. cell A1 contains the name Output (001000); cell A2 = Output
(001001) etc
Sheets("Output 1 (001000)").Select
Sheets("Output 1 (001000)").Copy

' Each new workbook would be saved with a similar name (e.g. 001001 School
Budget)
ActiveWorkbook.Saveas Filename:= _
"H:\Fin Management\Education, etc\Education 2009-10\School
Reports\Budget Reports\Current month\001000 School Budget.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks("001000 School Budget.xls").Close
' Once all worksheets have been copied and saved as a new workbook then
close the "master" workbook
Workbooks("School Budget Reports 08-02-10.xls").Close
Application.DisplayAlerts = True
End Sub


Thanks for any help