want to create pivot table using vba

[welchs101]

I have data (see enclosed file) and i want to create a pivot table using vba. I have created the table manually. I tried creating the table manually and but there is one aspect (maybe more) that i just dont understand.

There is a column of dates and when i put this into the "Row" field Years also shows up. Which is fine because i want to group by "Year" and "Month". Problem is i dont know how to do the grouping by year and month via vba.

Can someone help me?

if you need any additional information please just let me know.

[romperstomper]

You could start by recording a macro while creating the pivot table...

[welchs101]

i recorded the macro as you suggested (actually i had done this before you suggested it) and this is the code that is generated when i do the "grouping"

Please Login or Register  to view this content.

Two things:
(1) i dont understand this code at all ...........
(2) some how when i do the recording i dont know where in the code it adds the year and month..........i dont see this via the code. when i drag the "created date" into the row field it Automatically adds the "Year".
BUt i dont know where this is in the code nor do i understand it...........



here is all the code ...note i dont think i converted to the "count" of the contract number but i think i already know how to do this in the code.

Please Login or Register  to view this content.

[romperstomper]

That code assumes you already have a pivot table whose cache it can re-use. You'll need to delete all existing pivot tables and then record a macro creating a new one.

[welchs101]

when you say delete pivot tables..........i have several Files open and each of these files have a pivot table ......do i need to remove ALL pivot tables ffrom all the files?

[romperstomper]

No, just the file we are talking about. If you create more than one pivot table from the same source data, Excel will try and save resources by reusing an existing pivot cache. However, you probably need your code to create that pivot cache in the first place.

[welchs101]

i updated my code in another example i was using and IT WORKED.......but for some reason when i copy that code into the exmaple file i gave .......things just kinda fall apart ie...its not working.

Its getting stuck at this step and i have NO IDEA why.

Please Login or Register  to view this content.

the full code is shown below and is also in the enclosed/updated file under MODULE1.

Oh, one more question, a few times now when i run the macro and then hit debug to view where the error is ...i view the error then i hit the "square" button to end the macro.............
when i try to go back to the actual excel file its strange.......the cursor is doing funny things.......i tried resettign it by using in the immediate window

application.screenupdating = true

but this does not solve the cursor issue........thoughts?






full code for module1

Please Login or Register  to view this content.

[romperstomper]

You've specified 34 columns of data, but you only have 3. That would mean repeated blank field names in your pivot, which is not allowed, hence you get an error.

I don't really know what you mean by "the cursor is doing funny things"?

[welchs101]

THANKS!!!!!!!!!!!!! for figuring out that 34 cols vs 3 cols...........i was looking it over and over and over and just could not find anything......so thanks.


ok....so once i correct for the correct number of cols the run time error i get now is at the code

Please Login or Register  to view this content.

which is the reason i started this thread to beginwith.....after pasting this i realized the error at that step is due to the wrong col header value line of code should be as follows:

Please Login or Register  to view this content.

changing that line of code macro works...........


question: can you explain this last line of code what it is ........i am not sure i understand it at all

Please Login or Register  to view this content.

[romperstomper]

The array is the same list you get when you manually group a date column, with all the various things you can group by - Year, Month, Day, Quarter etc. Specifying True for any element means you want to group on that.

[welchs101]

not sure if this falls under this question or another.....if so just let me know and i will start another thread.......

see code below

Please Login or Register  to view this content.

while recording i manually removed subtotals and above is what it gave me...........not sure i understand this or how to apply it to my code.
i had some old code that (I did not understand see below)

Please Login or Register  to view this content.

and i am not sure how the two are similar or equal..........trying to understand this more than i do

[romperstomper]

The first one specifically turns off all subtotals for two fields; the second is a loop through any row fields there may be that are not called "Data". If you want to turn off all subtotals, it can be quicker to make sure a cell in the table is selected and then use:

Please Login or Register  to view this content.

[welchs101]

how do i make sure that cell is selected in the table.........i thought it was not a good idea to "select" cells when you do a macro?

[romperstomper]

It's generally not, and if you only have a few fields to deal with, it's probably not worth it, but if you have a lot, this can be significantly faster. You can use something like:

Please Login or Register  to view this content.

[welchs101]

this code ALMOST works. but problem is the "Years" column in the pivot.......for some reason pivotfields.count =3 and it does not see the "Years" column because it is grouped.....is my guess.......this code does not requirement to "select" a cell

but it does not work on the years column.............thoughts on how to fix this?

Please Login or Register  to view this content.

[romperstomper]

Post a workbook. You appear to either be referring to different pivot tables, or you're referring to the same pivot in two different ways, which is quite confusing.

[welchs101]

sorry about that.......thanks for you help!

i am enclosing an updated file ......it runs.......its just that it does not remove the "Years" subtotal.

Even if i put in the code that i got from recording (see below)

Please Login or Register  to view this content.

even this does not work.....and gives me a runtime error

[romperstomper]

Both codes from module3 work for me. Which one is failing for you?

[welchs101]

again i am sorry......its module 1 that i am running.......

[romperstomper]

That also runs without error here.

[welchs101]

yes it runs but the subtotal for years is still present......let me add in the code from the recording and it will have a run time error.

[welchs101]

if you look at the actual pivot table.........the "Years" subtotals are still there........go to bottom of pivot and you will see a total............go to bottom of 2020 and you will see a total

[romperstomper]

Nope. No subtotals there.

[welchs101]

huh? did you get a run time error?

[welchs101]

any ideas on why you would be getting the right answer with no runtime error and for me i get a runtime error.........i shutdown excel and opened it back up and stil get same error.........

[welchs101]

just wanted to confirm that you ran module1

[welchs101]

I copied the macro over to another computer and i ran module1.........and it gives me the same result that i found before......a runtime error when i have this in the code

Please Login or Register  to view this content.

runtime error 1004: "Unable to get the pivotfields property of the pivottable class"

[romperstomper]

I ran module 1. No errors, just a couple of messages. I was using 2016.

[welchs101]

thanks for responding...........two things
(1)how do i determine my version.........how did you detrmine your version
(2)can you run the module1 macro and then send me the output?i want to review it.

[welchs101]

to find the version i ran the following code in my vba

Please Login or Register  to view this content.

Version =16.0

[welchs101]

i am running 2016 on one of the ocmputers........the other is an office 365 and it does not show a version.

both are running a 32 bit software.....


can you send the file after you have run the macro....i would like to look at it.

[romperstomper]

You've put that line before the one that groups the dates...

[welchs101]

i dont understand what you mean by your last comment. Can you explain a little more what you mean by "You've put that line before the one that groups the dates..."

also, can you run the macro and send me the resulting file? i just want to see the output ............i have run this macro on 3 machines now and get the same result....

[romperstomper]

I mean you've put the line that turns off the subtotals on the Years field before the line that groups the dates - and that's the one that creates the Years field.

[welchs101]

Thanks!!!!!!!!!!!!!!!!! that was it. i moved the "turnoff" subtotals "after" i did the grouping thing and it worked.....i am enclosing the final file/macro that worked in case someone was interested.

thanks!!!!!!!!!!!