Summarize report with VBA

falcon771

New Member
Joined
Aug 2, 2005
Messages
10
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
ABCD
1ProductNumberShipPeriodIdShippedQty
228979-00-B2007M044
328979-00-B2007M053
429116-002007M055
529118-102007M051
629127-502007M053
736219-00-C2007M014
836219-00-C2007M021
936219-00-C2007M053
1036311-00-D2007M013
1136311-00-D2007M021
1236311-00-D2007M031
1336311-00-D2007M057
1436311-00-D2007M073
15Summary39
16Aug16,2007-1-3:37:10PM
Page1-1


I would like the summary to be on a new page and look like this
Service2007Consumptiontest.xls
ABCDEFGHI
1ProductNumberJan2007Feb2007Mar2007Apr2007May2007June2007July2007Aug2007
228979-00-B43
329116-005
429118-101
529127-503
636219-00-C413
736311-00-D31173
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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

klb

Well-known Member
Joined
Apr 3, 2002
Messages
821
Have you tried running a pivot table on either the original data or the report?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

New Member
Joined
Aug 2, 2005
Messages
10
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,622
Messages
5,625,931
Members
416,143
Latest member
JoyceMB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top