Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 25

Thread: Creating Pivots in Excel VBA and Create an Email

  1. #11
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  2. #12
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Here's the file I worked on
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #13
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    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.

  4. #14
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    Files Attached
    Attached Files Attached Files

  5. #15
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    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.

  6. #16
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Looking at this now.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #17
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    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.

  8. #18
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #19
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    Thanks for your help.

  10. #20
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    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?

Similar Threads

  1. Replies: 17
    Last Post: 07-15-2013, 09:56 PM
  2. VBA Code To Email Multiple Recipients From Excel
    By cdurfey in forum Excel Help
    Replies: 4
    Last Post: 06-11-2013, 12:18 AM
  3. Replies: 2
    Last Post: 05-23-2013, 08:08 AM
  4. Replies: 2
    Last Post: 03-12-2013, 02:57 PM
  5. Creating drop-down function in excel
    By Jorrg1 in forum Excel Help
    Replies: 4
    Last Post: 01-09-2013, 01:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •