Update list each time new file is created.

[Tech.Zoan]

Is there a way to update a job list automatically in one spreadsheet
each time I create a new spreadsheet for a job?

For example...

Everytime we start a new job, I create a spreadsheet (a template) to
keep track of the timesheets and job costs. Each spreadsheet is named
using the project name.

I need a seperate spreadsheet that will automatically add a new line
to the list based on the creation of the new job spreadsheet as
mentioned above. This spreadsheet will list all the jobs, based on the
existance of a spreadsheet file for that job. In other words... for
each spreadsheet file that exists in a specified folder, this
spreadsheet would create a line for each job and include some amounts
found in each job file (I use a template so cell reference should not
be an issue).

The job tracking spreadsheet will keep track of all the jobs for a one
year period so it will be several hundred lines long as the year
progresses.

Can this be done easily?

 

[Gord Dibben]

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

 

[Nozza]

Can this be done easily?

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

 

[Tech.Zoan]

Thanks Nozza... I have a feeling your macro could be modified to do
what i need... unfortunately, I've never really worked with macros so
i don't even know where to begin :-(

Maybe if i e-mailed you a template of my two excel files that I use,
you could write me the macro for it **bats eyelashes

In any event, I do thank you for taking the time to respond.

 

[Nozza]

Thanks Nozza... I have a feeling your macro could be modified to do
what i need... unfortunately, I've never really worked with macros so
i don't even know where to begin :-(

Maybe if i e-mailed you a template of my two excel files that I use,
you could write me the macro for it **bats eyelashes

In any event, I do thank you for taking the time to respond.

No worries - drop them through and post a message here - I'll check
the email

Noz