Pivot Table to summarize data in a table

[ppalmerirs]

Please see attached. We currently have a spreadsheet that we use for monthly projections based on client. We are a healthcare business, so we have billing date, expected receipt date, etc. What we are trying to do is take the projected receipt date and expected amount for each client and put it on a summary tab and then sort by oldest date to newest date in order to know when cash will be coming in.

If you see on the attached worksheet, I tried to do a pivot table to summarize the data I want, which I would do for each of the client tabs, but I am wondering how do I summarize the data on another tab? Would I have to link it or should I make the pivot table on another tab, etc. Please help. This is a problem that I have not been able to solve and it is creating a lot of manual work.

[p24leclerc]

You usually put all clients in the same tab for this kind of data. You'll need to add at least one column for the client's name. This way you can create a pivot table (in a new tab) that would summarize all of your data at once. Is that possible for you?

[ppalmerirs]

I can add a column to each tab with the client name. Then what do I do? Move each pivot table to a new tab?

[p24leclerc]

Look at the attached workbook where I copied your client tab multiple times for a test.
The button in the summary sheet will run a macro that will copy data from every client's tab into Summary. It adds the client's name after the last column in Summary.
From this Suymmary sheet, you just have to create a Pivot table into a new sheet and configure it to get your desired report.

Does this work for you?

[ppalmerirs]

Yes, thank you. Is there anyway to put the client name in the left column or do I have to modify the client sheets and put the client name in column A first? Can you give me instructions to copy the macro to other monthly sheets? Thank you!

[p24leclerc]

Here is the macro you can copy to other workbook.

Please Login or Register  to view this content.

To do so, you go into the Visual Basic Editor by clicking ALT + F11. You should see, on the left side of the screen, the list of opened workbook along with the name of all their sheets. First, make sure you click on your workbook you want to copy the macro to. Then you go tho the top menu and click on Insert then on Module. Now, you double click on the created Module (if it's the first one, it will be named Module1) and paste the attached macro.
Now, you don't have the button to trigger the macro but you can still run it by clicking on the View menu then, on the right side of the menu, you click on the Macros menu and then on View Macros. In the window Macro, look for your macro named Gather_Data and click on Run.

Attached is the new workbook with the client's name in column A.

Hope this helps

[ppalmerirs]

I haven't tried it yet, but thank you so much! This is more than awesome!

[ppalmerirs]

It gave me this error, when I ran the macro once I put it into my spreadsheet with all the client tabs. What did I do wrong?

macro error1.jpgmacro error.png

[p24leclerc]

Can't tell from the screen. Can you attach your workbook so I can try it myself?

[ppalmerirs]

I think I have it working now. Thank you.

[ppalmerirs]

Thank you. Thank you. Thank you. This is making our lives much easier already! I will post if I have any other questions, but so far I am able to make it work on each monthlyworkbook.