Creating Pivots in Excel VBA and Create an Email

The code probably errors out because the name of the sheet used in the code is not found in the workbook you are trying to run the code from. Where have you pasted the code? Is it in the personal file? If yes, then in the code above, instead of ThisWorkbook, use ActiveWorkbook

Here's the file I worked on

It worked in the Test file that i had attached. But when i use the code in my main personal file, it throws the error "Method 'CreatePivotTable' of object 'PivotCache' Failed.

I am attaching my main personal file and the complete code after which i want to use the pivot code.

Files Attached

Hi - sorry to bother. Did you get a chance to look into this. when i post on a blank module, the code works correct. but when i post it as a part of my complete macro, it throws the error.

Looking at this now.

i guess i have found the reason. my file is an export from a web link. once i save it to any filepath and then run the code, it works perfect.


If i have to add more pivots and more criteria, how do i play with the code as i am unable to understand the code that much.

Glad you got it to work. The code first adds a pivot cache, and then out of the pivot cache, it runs 3 loops and creates the three pivot tables. And since you were keeping everything constant except the column fields, I used an array, with those 3 names, and in the loop, just built each pivot in the same way, and just positioning it 2 rows below the last filled row. Just execute the code line by line, and you'll figure it out.

Thanks for your help.

Hi...In the pivot, for e.g. in the third pivot, i dont have a comment "Move Amount Mismatch", and everything is "OK" in the data, is it possible for the pivot not to appear?