Creating reports from data

[Len Silva]

I am struggling to learn both VBA and Pivot Tables.
I would like to create a button for the user to create a report from the attached file.

For instance TOTAL by month (from DOS)

or BAL DUE by Month and NAME.

or INSPAY by INSCO by month

I would like the report to be on a new sheet called REPORTS

The file is just a sample with names changed. The actual file is about 400 lines and will expand to 1500 0r so by years end.

I'm hoping someone can get me started so I can learn and expand myself.

Thanks for all your help,

Len Silva

[Richard Buttrey]

Hi,

Does the attached help. Just drag and drop the Pivot Table field names around as necessary. This is just one view but of course you can dice and slice the data any way you want.

I've created a dynamic range name that will automatically expand or contract to cover all your data so you don't need to worry about adding records. I've also put a simple pivot table refresh macro in the Reports sheet Activate event so that every time you select the sheet it will refresh automatically.

Hence no button is necessary.

[Len Silva]

That works well for me (thanks), but I need to make it easy for a very inexperienced user. Your post does go a long way toward helping me understand Pivot Tables.

[Richard Buttrey]

That works well for me (thanks), but I need to make it easy for a very inexperienced user. Your post does go a long way toward helping me understand Pivot Tables.

Not quite sure how much easier it can be given that all the user has to do is add new data - as they do now. Everything else is automatic as I explained.
They don't even need to press a button which was one of your suggestions. I'm presuming you will have set the Pivot Table layout up before you hand it on the the user.

Can you describe what you think can be done to make it easier for the user?

If you want the user to drag and drop the Pivot Table fields around to see different reports then you'll just need to show them how. It's not difficult. The other option would be to have as many separate pivot tables as you have reports although that does seem a backward step since they would all feed off the same data. The only other choice is some buttons, one for each report type so that each button would move the single Pivot Table fields around for you with a macro. All of which is not as elegant since the whole point of PTs is that merely by clicking dragging and dropping you can create the report you want.

[Len Silva]

Actually, (as I should have noted), the user never actually sees this sheet (named Master). It is generated by a macro from many other sheets in the workbook, one for each NAME. I will probably keep it hidden. That macro was kindly given to me by another member and it works very well.

What I would like to end up with is a printable report, perhaps sent to a pre-formatted sheet or even exported to a formatted Word Doc.
I will use the macro you provided to create the PT, but then I would like the report in a new sheet. The "button" will be on sheet1.

I think that if someone can help me build just 1 report , say TOTAL by MONTH, then I can take it from there.

Thanks again.

[Richard Buttrey]

The attached is a PT, on its own sheet, showing Totals by month.

I'm not clear what you mean by a 'Printable Report' if not what you see hear. Perhaps if you offer a mocked up example of what you want to see we may be able to advise further.

If you want a completely separate workbook then a simple macro could export the PT sheet to a new book, but we do need to see exactly what you expect to see.

[Len Silva]

Thanks,

I am going to mark this solved for now and revisit it later. I need to better define exactly what the user is looking for.