Have you tried the Template Wizard with Data Tracking?

Writes each newly created workbook record to a data.xls workbook.

Can be downloaded from

http://support.microsoft.com/kb/873209/en-us

Gord Dibben  MS Excel MVP

I have something I think is similar - I need to create new assignments
for students all the time and like to be able to keep track of them.

Here's the code from my macro - probably not bullet proof - but it
works for me

Sub CreateNewAssignment()
'070202 Manage the new assignment sheets

   'Declarations
   Dim MyDate, MyFileName
   Dim NextRow As Long
   
   'Determine the new filename - yymmdd description
   MyFileName = Format(Date, "yymmdd") & " " & InputBox("Enter an
assignment description")

   'Create new workbook based on NewAssignment.xlt
   Workbooks.Add Template:="H:\Data\Templates\NewAssignment.xlt"
   
   'Save the workbook with the filename
   ActiveWorkbook.SaveAs Filename:=MyFileName, _
       FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
       ReadOnlyRecommended:=False, CreateBackup:=False
   
   'Close the new workbook to return to main sheet
   ActiveWindow.Close
   
   'Add details of new workbook to sheet
   'Determine the next free row
   NextRow = Range("A65536").End(xlUp).Row + 1
   
   'Add the name of the new workbook
   Cells(NextRow, 1) = MyFileName
   
   'Ensure cell filename written in is selected cell
   Cells(NextRow, 1).Select
   
   
   'Add a hyperlink to the new workbook
   ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
   "H:\Data\Excel\" & MyFileName & ".xls", TextToDisplay:=MyFileName
   
End Sub