Trying to create a pivot table with a macro

[zookeepertx]

Hello all!

I'm trying to create 2 pivot tables on a spreadsheet; both tables will use the same data. This would probably be going better if I had a good handle on pivot tables, but I still had delusions of creating this macro without help. That plan has failed.
I've tried and tried to get my brain wrapped around the darn tables without success. Reading instructions does me no good; I need to be hands on and nobody around here knows anything about them either!

So, anyway, with that said - I tried recording a macro to create the 2 tables needed but, of course, it didn't work. I've been Googling the better part of the afternoon and keep altering and tweaking the code, just trying to get it to create ONE of the tables, thinking I'd move on from there. I've made such a mess of the code that it's not recognizable anymore, so I've trashed it.

I'm attaching a very shortened version of what's needed. Basically, I need to select A-R and down to the last row, sort by M, then make the tables. The first one needs to start in U2, having U showing the DIVISION and T showing the count of lines in each division. The second table needs to start in X2, having X showing the DIVISION divided by Vendor Name and Y showing the count of each vendor by division.

I'm sure I'm over-thinking it and you all will get it right off. I have all the faith in the world in you folks!

Thanks

Jenny

[raman1667]

Hi Jenny,

PFB for the required code and PFA for the sample data and macro file. I hope this code helps.

Please Login or Register  to view this content.

Regards
Ramandeep Singh

[zookeepertx]

Hello!
Thank you very much for your reply! I will be back at work on Monday and will give it a try then. I'll be sure to come back and let you know how it goes.

Jenny

[zookeepertx]

Sorry for being so long in getting back to you, but it's Month End here at work and I'm in the Acctg Dept, so I've been swamped.

So, I tried your coding and it gives me part of what I need. I did have to replace DefaultVersion:=xlPivotTableVersion15 with Version 14 to get past an error message. (I'm on 2010)

Just a couple of problems, though. I'm re-attaching my workbook to show the trouble. As before, sheet 1 shows what I need; Sheet 3 shows what your coding gives me. In the second table, the vendor names need to be oriented under the divisions, but in the same column. (See sheet 1). And the "Count of..." column (which should be column "Y") needs to be a count of each vendor within each division. Also, at the bottom of each table is a row that says (blank); can we get rid of that?

I've been googling, tweaking, recorded and re-recorded macros trying to hit just the right wording in the code, but I'm going crazy! I'm sure you'll see what's wrong right away; definitely faster than I EVER would!

Thanks for your help!

Jenny

[zookeepertx]

Does anyone have any input on this problem? I spent about half a day on Friday trying again to get it to work correctly and got absolutely nowhere!