Creating a running total sheet + Pivot Tables

Nobby · Apr 8th, 2002, 03:36 AM

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 · Apr 8th, 2002, 03:42 AM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 8th, 2002, 03:36 AM	#1
Nobby Board Regular Join Date: Feb 2002 Location: United Kingdom Posts: 68	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

Apr 8th, 2002, 03:42 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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 ]