Tracking New Tabs in Excel

[John M]

Hello,

I am trying to set up a way to track information in a "tracking tab" for new
tabs that I add for quotes on new business. So, we have a basic template
that we copy 2-3 times a day to develop new quotes. I want to extract key
info from each quote in this tracking tab (date of quote, customer, $ amount,
etc.).

The problem I have is that each time I add a new tab I need to manually copy
my formula down one line in the tracking tab and edit the formulas to link to
the new tab.

Is there a way to set up the formulas in the tracking tab so that when we
add a new tab for a quote the summary information will automatically populate
a new line in the tracking tab.

Any help is much appreciated.

Regards,
John M

 

[Gord Dibben]

Sounds a job for the Template Wizard with Data Tracking.

You create a Template workbook for your quotes.

Each new quote information is added to a database workbook.

The new quote is saved as its own workbook.

It is difficult to obtain the Wizard on-line but if you email me I can send
you a copy.

Was designed for earlier version of Excel but I have suceesfully used it in
Excel 2003 and 2007.

My email is gorddibbATshawDOTca change the obvious.


Gord Dibben MS Excel MVP

 

[Tim Williams]

How about something like this (in the tracking worksheet code module)

Tim

'********************************
Private Sub Worksheet_Activate()

Dim s As Excel.Worksheet
Dim r As Long


Me.Range("A2:C50").ClearContents
r = 2
For Each s In ThisWorkbook.Worksheets
If s.Name <> "TemplateSheet" Then
If s.Name <> Me.Name Then
With Me.Rows(r)
.Cells(1).Value = s.Name
.Cells(2).Value = s.Range("B3").Value
.Cells(3).Value = s.Range("C5").Value
End With
r = r + 1
End If
End If
Next s

End Sub
'*******************************