Prev: Macro to copy a VLOOKUP formula - Data with a filter on
Next: Excel VBA: Reading data from HTML-file
From: Simon on 23 Mar 2010 05:40
Workbook “Sales”
(A1)Product (B1)Qty (C1)Date
X 2 2/1/2010
X 1 10/1/2010
X 3 5/3/2010
Y 1 8/1/2010
Y 2 5/2/2010
Y 1 3/3/2010
Y 1 5/3/2010
Z 2 3/2/2010
Z 1 5/3/2010

Workbook “Report”
SUMQTY
(A2)Product (B2)Mar10 (C2)Feb10 (D2)Jan10
X 3 0 3
Y 2 2 1
Z 1 2 0

Hi
“Sales” is a excel workbook from our sales system.
I would like some code to organise all the sales data found in “Sales” and
group it into monthly columns in a new excel file “Report” but being new to
vba I don't know where to start.
In the workbook “Report”:
I want B2 to be the current month and year (Mar10), C2 to be the current
month -1 (Feb10) and so on until I have 12 columns i.e 1 year.
Then I want the SUM of all the QTYs for each month for each product in the
correct column as shown above.
Can anyone help with the code?
Many thanks

Simon
From: Roger Govier on 23 Mar 2010 06:07
Hi Simon

Don't use code, use a Pivot Table

Following instructions are for XL2003 (write back if you want XL2007)

Place cursor in your data data table on Sales
sheet>Data>List>Create>click my List has headers.
Data>Pivot Table>Finish
On the new sheet created, with a PT skeleton
Drag Date to the Row area
Drag Product to the Row area
Drag Qty to the Data area

Place cursor on any Date>Right click>Group and Outline>Group>click
Months and Years>OK
Drag Years to the Page area
Drag Months to the Column area

Having created a List first, the source data for the PT will grow
dynamically as you add more lines of data.
--
Regards
Roger Govier

Simon wrote:
> Workbook “Sales”
> (A1)Product (B1)Qty (C1)Date
> X 2 2/1/2010
> X 1 10/1/2010
> X 3 5/3/2010
> Y 1 8/1/2010
> Y 2 5/2/2010
> Y 1 3/3/2010
> Y 1 5/3/2010
> Z 2 3/2/2010
> Z 1 5/3/2010
>
> Workbook “Report”
> SUMQTY
> (A2)Product (B2)Mar10 (C2)Feb10 (D2)Jan10
> X 3 0 3
> Y 2 2 1
> Z 1 2 0
>
> Hi
> “Sales” is a excel workbook from our sales system.
> I would like some code to organise all the sales data found in “Sales” and
> group it into monthly columns in a new excel file “Report” but being new to
> vba I don't know where to start.
> In the workbook “Report”:
> I want B2 to be the current month and year (Mar10), C2 to be the current
> month -1 (Feb10) and so on until I have 12 columns i.e 1 year.
> Then I want the SUM of all the QTYs for each month for each product in the
> correct column as shown above.
> Can anyone help with the code?
> Many thanks
>
> Simon
From: Simon on 23 Mar 2010 13:13
Hi Roger
I am also pulling other data into the "report" from other spreadheets so
think I need to vba.

"Roger Govier" wrote:

> Hi Simon
>
> Don't use code, use a Pivot Table
>
> Following instructions are for XL2003 (write back if you want XL2007)
>
> Place cursor in your data data table on Sales
> sheet>Data>List>Create>click my List has headers.
> Data>Pivot Table>Finish
> On the new sheet created, with a PT skeleton
> Drag Date to the Row area
> Drag Product to the Row area
> Drag Qty to the Data area
>
> Place cursor on any Date>Right click>Group and Outline>Group>click
> Months and Years>OK
> Drag Years to the Page area
> Drag Months to the Column area
>
> Having created a List first, the source data for the PT will grow
> dynamically as you add more lines of data.
> --
> Regards
> Roger Govier
>
> Simon wrote:
> > Workbook “Sales”
> > (A1)Product (B1)Qty (C1)Date
> > X 2 2/1/2010
> > X 1 10/1/2010
> > X 3 5/3/2010
> > Y 1 8/1/2010
> > Y 2 5/2/2010
> > Y 1 3/3/2010
> > Y 1 5/3/2010
> > Z 2 3/2/2010
> > Z 1 5/3/2010
> >
> > Workbook “Report”
> > SUMQTY
> > (A2)Product (B2)Mar10 (C2)Feb10 (D2)Jan10
> > X 3 0 3
> > Y 2 2 1
> > Z 1 2 0
> >
> > Hi
> > “Sales” is a excel workbook from our sales system.
> > I would like some code to organise all the sales data found in “Sales” and
> > group it into monthly columns in a new excel file “Report” but being new to
> > vba I don’t know where to start.
> > In the workbook “Report”:
> > I want B2 to be the current month and year (Mar10), C2 to be the current
> > month -1 (Feb10) and so on until I have 12 columns i.e 1 year.
> > Then I want the SUM of all the QTYs for each month for each product in the
> > correct column as shown above.
> > Can anyone help with the code?
> > Many thanks
> >
> > Simon
> .
>
 | 
Pages: 1
Prev: Macro to copy a VLOOKUP formula - Data with a filter on
Next: Excel VBA: Reading data from HTML-file