Creating a running total sheet + Pivot Tables

[Nobby]

I am creating a workbook with 12 sheets representing monthly data. The coumns will remain constant but the number of rows will change each month.

Based on this monthly data, I am creating 3 separate Pivot Tables/Charts.

So, the help I need is on the following points:

1. I want to also create an annual running total sheet. Can anyone help me with a macro that will copy the current month's data and paste it in the first empty row on the Annual Summary sheet? Ideally, the macro would contain a question box allowing me to enter the name of the month so that the data from the correct worksheet is copied across.

2. Is there a way of setting up the Pivot Table so that I can change the data range easily by amending the month name, thereby having the Pivot Table/Chart showing data from the current month only?

Thanks

Nobby

 

[Dave Hawley]

Hi Nobby


I think you will find the answer to the changing month range here:

http://www.ozgrid.com/Excel/DynamicRanges.htm

The total by month could then easily be handled via a Pivot Table that is grouped by Month.

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-04-08 02:50