Summarize report with VBA

[falcon771]

I have a report that I need to summarize. I get a list of parts that have shipped and each month the part ships is a different line in the report. I would like to summarize it so each line is a part and then each column shows the quantity shipped each month. For example, this is what I get:

Service2007Consumptiontest.xls
	A	B	C	D
1	ProductNumber	ShipPeriodId	ShippedQty
2	28979-00-B	2007M04	4
3	28979-00-B	2007M05	3
4	29116-00	2007M05	5
5	29118-10	2007M05	1
6	29127-50	2007M05	3
7	36219-00-C	2007M01	4
8	36219-00-C	2007M02	1
9	36219-00-C	2007M05	3
10	36311-00-D	2007M01	3
11	36311-00-D	2007M02	1
12	36311-00-D	2007M03	1
13	36311-00-D	2007M05	7
14	36311-00-D	2007M07	3
15	Summary		39
16	Aug16,2007	-1-	3:37:10PM
Page1-1

I would like the summary to be on a new page and look like this

Service2007Consumptiontest.xls
	A	B	C	D	E	F	G	H	I
1	ProductNumber	Jan2007	Feb2007	Mar2007	Apr2007	May2007	June2007	July2007	Aug2007
2	28979-00-B				4	3
3	29116-00					5
4	29118-10					1
5	29127-50					3
6	36219-00-C	4	1			3
7	36311-00-D	3	1	1		7		3
Page1-1

I would like it if the month would automatically progress automatically, so I do not have to go in and change it each month. The other problem is the report could be 100 lines, or 500 lines. That si the trouble I am really having. I can to a macro to go through the steps, but each month it changes and each month the number of lines changes. My macro is not working. I was told this NEEDS to be VBA, but I have zero VBA skills.

 

[klb]

Have you tried running a pivot table on either the original data or the report?

 

[HalfAce]

Hello falcon771,
Yes, that sounds "do-able". A few background questions first though.
1) In your posted screen shots, what are the actual names of the sheets shown?
2) Next time you receive the list shown in your first example, will it be an all new list? - or
will it be the same list with additions/deletions made to it?
3) Does your 'Summary' sheet already exist? - (with the layout shown above in your second
example) or does that need to be created on the fly?

Probably come up with some more questions before it's done, but for what I'm thinking
it would be good to have these answered first.

 

[falcon771]

Hello falcon771,
Yes, that sounds "do-able". A few background questions first though.
1) In your posted screen shots, what are the actual names of the sheets shown?
2) Next time you receive the list shown in your first example, will it be an all new list? - or
will it be the same list with additions/deletions made to it?
3) Does your 'Summary' sheet already exist? - (with the layout shown above in your second
example) or does that need to be created on the fly?

Probably come up with some more questions before it's done, but for what I'm thinking
it would be good to have these answered first.

The actual sheet name of the original report is "Page1-1".

The list will be named the same each time I get it.

The summary sheet is created on the fly.

thanks