Organising sales data by month

[Simon]

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

 

[Roger Govier]

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.

 

[Simon]

Hi Roger
I am also pulling other data into the "report" from other spreadheets so
think I need to vba.