Tracking New Tabs in Excel

  • Thread starter Thread starter John M
  • Start date Start date
J

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
 
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
 
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
'*******************************
 
Back
Top