How do I make a Pivot Table query in VBA code

[psionic]

Hi All, Happy New Year

Another situtation of coding needed here guys, I had in mind this to be an Add-In so I can use this frequently (any advice how to do this is appreciated)

I am not truely 100% familiar with the VBA coding side of doing a Pivot Table so I am asking anyone in this wonderful universe we have to help me out while I am working this normally or manually by point and click.

I need to automatically generate a report that Pivot Tables does very nicely for each case in question and the file can be saved as new Excel file using" Errors Report 11012008.xls" file pattern from a database dump of data and finishing up with a summary page of all my results completed for me to look at and save.

So listen up folks...Here is the situtation, I make up a fictional 3 Fruits Department so I can explain the concepts. All 3 different departments all share same basic details like Status, Sale Person Name, Price, Unit Produced and Flag for Quality Assurance.

The data looks something like this.

[vba]Row A B C D E
01 Dept Status SalePerson Price Unit Flag
02 Bananas Packaged Mr Happy 5.30 34 Y
03 Apples Packaged Mr Green 10 23 Y
04 Oranges Packaged Mr White 15 28 Y
05 Bananas Picked 5.30 34 N
06 Apples Picked Mr Green 13 14
07 Oranges Picked 35 Y
08 Bananas Organised Mr Happy 39
09 Apples Organised Mr Green 10 N
10 Oranges Picked 5 Y[/vba]

So when the VBA reporting is finished I will have new excel spreadsheet with 4 tabs - (All of rows/columns are formatted to AutoFormat - List 3 as this is been proven easier to read) 1 tab named Bananas, 1 tab named Apples, 1 tab named Oranges and a Summary Report summarising all others reports (this is dynamic as I don't know which one shows up at any time) and what is the number of rows counted for each according to field named "Status" that is Packaged, Picked, Organised.

In Each tab, (in Pivot Table Lingo) Any fields with Missing information are in listed in Row Area, All fields shown in Column Area and Data Area shows all information that you can see what is missing.

In tab Bananas there are 2 rows for missing Sales Person and missing Price

In tab Apples there are 2 rows for missing Price and missing Flag

In tab Oranges there are 2 rows for missing Sales Person and missing Price fields

In tab Summary shows a report in two parts.

Part 1 (in Pivot Table lingo) By page is department

Missing Fields (there are found missing, Sales Person - Price - Flag. Unit does NOT show up as there is nothing wrong with that column) in Rows Area, Status in Column Area and the Count of the fields found in Data Area.

Grand Total are shown as total count including another additional Row for a percentage (Total of all Units / Departments) by Department out of three so this helps prioritisation to fix major fires with.

Part 2 (in Pivot Table lingo) By part is department

Missing Fields (there are found missing) in Rows Area, By Sales Person in Column Area and Count of fields found in Data Area

Grand Total are shown as total count with a percentage by Person so we know who's the culpit to fix up.

Hope this clarifies my requirements and I can change the VBA code to suit the real fields I use later.

Good luck coders.

Cheers, thanks and warm regards,
Psionic from Down Under.

[Aussiebear]

G'day psionic, I've just picked up a book "Pivot Table, data crunching" by Bill Jelen & Michael Alexander, published by Que. This book retails for $39.95 here in Australia.

Some 300 pages in length, and coupled with files that can be downloaded from http//:www.mrexcel.com/pivotbookdata2007.html. Towards the latter half of the book the authors provide plenty of examples of vba coded reporting methodology for creating pivot tables etc.

You should try and locate one. Its well worth a look.

[psionic]

Thanks Aussiebear.

I checked out @ Amazon.com and looks good.

Just waiting for my pay check to come in the mail so I can spend some cash on this and will check it out in the store near me. Possibly @ Southland or Chastone.

I'm still open to anyone who can help though.

Psionic.

G'day psionic, I've just picked up a book "Pivot Table, data crunching" by Bill Jelen & Michael Alexander, published by Que. This book retails for $39.95 here in Australia.

Some 300 pages in length, and coupled with files that can be downloaded from http//:www.mrexcel.com/pivotbookdata2007.html. Towards the latter half of the book the authors provide plenty of examples of vba coded reporting methodology for creating pivot tables etc.

You should try and locate one. Its well worth a look.