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 dont 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
sheetDataListCreateclick my List has headers.
DataPivot TableFinish
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 DateRight clickGroup and OutlineGroupclick
Months and YearsOK
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 dont 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 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
sheetDataListCreateclick my List has headers.
DataPivot TableFinish
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 DateRight clickGroup and OutlineGroupclick
Months and YearsOK
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
.