Macro for a Summary of multiple worksheets

Hey Everyone,

I am familiar with the basics of Excel, but not with Macros or VBA's.
I am trying to create one Excel file for accounting purposes. Within this file I want to create a worksheet for each customer I have in my store. On this sheet I want to list my costs and their payments. I would like to then create a summary sheet which adds all of the individual customer sheets together. Now this is easy to do manually, but what I need to create is an automation (Macro I assume) that does the following:
I add a new sheet and name it after the new customer. The Macro automatically adds that sheet into the formula for the summary sheet. This way may secretary only needs to activate the macro, which will copy a sheet and automatically update the summary page to include this newly copied sheet and all its information. Now the process for automating projected costs and profits is very easy and requires nothing more but to activate the macro.

Any help would be appreciated. I couldn't get it to work.

Here is another way of explaining it, maybe my first post was too vague.

I have an accounting Excel file. This file will have a sheet for each customer my business has. Each sheet (for each customer one), will list all his payments and all my costs and then totals them on the bottom of the sheet. Now I will create one sheet like this for every customer I have. Another sheet I have is a summary sheet where all the costs and payments of all customers are totaled together to give me various statistics for projecting profits and costs from all customers. Now I can manually program a SUM in the summary sheet and reference each individual sheet. But what I would need is the following: I need a Macro that will automatically reference a newly created sheet (customer) in the SUM of the summary sheet so that if I have a new customer all I need to do is copy the last customer sheet, change the data on it, run the macro (wherever that would be necessary) and the summary sheet automatically accepts it.

How is that possible?

Hello stylist and welcome to the forums

as a new member you may not have seen the many requests to new posters, please attach a sample workbook with your input data and exactly what you expect clearly shown .

most of us here are volunteers so anything that makes it easy for us to help you will increase your chances of a quick response and often, a spreadsheet back with the solution included for you to copy into your real workbook. (most of us will struggle with the need to create a workbook that matches yours so that we can test any code, so maybe we just pass the thread

To post a file, use the "Go Advanced" button in the lower right corner of the posting screen

jmac1947

Here is a file attached of the exact sheet I need macroed.

stylist,

what is max likely number of sale to a single customer likely to be?
do they tend to buy in different categories at a single sale?
do they buy multiple types in a single category or are you simply capturing he total of say three "packages" as one line item in the customer worksheet?

Just a couple of scope questions as I think how to build a useful solution?

jmac

This copies all the customer data to the summary page.

Please Login or Register  to view this content.

Hey guys,
Thank you for the responses.

Each table ie. Packages & Labs & payments etc are on a monthly basis. So they themselves will never exceed more than 10. The summary chart now reflects the totals and then does the quarterly and annual projections. This is manually designed already as you can see. I now need the macro to automatically add any sheet that gets copied from the existing one into the formulas of the summary sheet, so that I don't have to manually add all of them, as my secretary will be doing it and she doesn't know how to do it.

How do I activate the worksheet?
Pardon my ignorance on the subject matter, but I couldn't test it because I didn't know how to activate it.

If that is the case, then the code provided above does not work unfortunately.
I added a sheet and filled out some arbitrary numbers and then saved and even closed the excel file and reopened it again, but no calculations were done on the summary sheet. Currently it doesn't even update the existing sheets.

Attached sheet from above

Hey bulina2k,
Thank you very much. It seems to work perfectly.
Can I add infinite amounts of sheets to this file?