Organising sales data by month

  • Thread starter Thread starter Simon
  • Start date Start date
S

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
 
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.
 
Hi Roger
I am also pulling other data into the "report" from other spreadheets so
think I need to vba.
 
Back
Top