Macro's - inserting a worksheet and having upload data

[Guest]

I have a template worksheet that has data on it that a user is required to
input. I also have a macro that makes a copy of the template worksheet. For
arguments sake, the copy of the template worksheet may be named "Bob", and
the user can then input information relating to Bob. An example may be, how
much money Bob has.

I then have a summary worksheet that summarises all of the data in the
individual worksheets that are created from the template. One of the cells
may sum the same cell on all of the worksheets in the spreadsheet that have
been created from the template worksheet.

I want to have the flexibility to create a new worksheet from the template
worksheet and have the summary worksheet automatically link in the
information from the new worksheet. So, the question is, once i have created
the new worksheet from the template and input the data relating to that
particular person, how can i have it link directly into some sort of summary
worksheet without having to go and change the formulas in the summary
spreadsheet each time a new worksheet is added.

 

[Tom Ogilvy]

in the tab order

Start bob mary john Last Summary

leave sheets Start and Last Blank

In summary put in a 3D formula (in cell B9 for example)

=sum(Start:Last!B9) for example. Then drag fill down and across on the
summary sheet.

Now, any sheet you move between Start and Last in the Tab order will be
included in Summary.

 

[Guest]

What you need is a loop that is triggered by ,say a button that is on your
Summary sheet - I'll call it Master. In the Master sheet lets say you want
cell A1 = the sum of cell A1 in all other sheets. Try this:

Sub AddEmUp()

Dim Sh as Worksheet
Dim Total as Long

For each Sh in Worksheets
If Sh.Name<>"Master" then
Total = Total + Range("A1")
End if
Next
Sheets("Master").Range("A1") = Total
End Sub