+ Reply to Thread
Results 1 to 7 of 7

Macro to Create Pivots

  1. #1
    Registered User
    Join Date
    04-13-2015
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    3

    Macro to Create Pivots

    I need to record macros that create the various pivots on the attached sample data. My source data is coming from SalesForce to Excel. The report is exported from SF every week and run on a rolling 52 week time frame. In addition to the screenshot below, I recreate that page with the omitted reasons in their own pivots then make 12 week and 52 week line charts with markers for each.

    Since the tables will be created from a different report (but with exactly the same column layout) each time I want the macro to be saved in my personal.xls space so that I can run it each week regardless of the date.

    I'm quite tired of doing this manually each week and would be very grateful for any help that will speed up this weekly tedious task. I haven't been able to figure out VBA coding on this level despite all my efforts and am now throwing up my hands and asking for help from the community. I've attached dummy data to illustrate what I need help with.


    ***I tried uploading the sample data with the pivots already done but the file was too large.
    Credit Cycle Screen Cap.JPG
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Macro to Create Pivots

    before looking at your sheet

    i like to refer to this site for beginners of VBA with Pivot table
    https://www.thespreadsheetguru.com/b...l-pivot-tables

    not affiliated with site in any way just think its written in a manner which is easy to understand (assuming you have basic VBA knowledge)
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Macro to Create Pivots

    looking at your example file...i find it hard to determine exactly what your pivot table is set to
    can only figure out you have
    reason on the Report filter
    week ending as row label (grouped by days of 7?)
    and values = average of cycle time

  4. #4
    Registered User
    Join Date
    04-13-2015
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    3

    Re: Macro to Create Pivots

    That's correct. All of the pivots except one will use the average cycle time with the data grouped by week ending date. My week runs from Sunday - Saturday. The reason filter removes any items coded as Late Fees, Lost Mgmt, Refund, Site Sold and Site Transfer.

    Also thank you for the link. I will take a look at it now.

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Macro to Create Pivots

    not sure exactly how you group your week ending date

    here is a code that does what you have just said
    this is one pivot table
    you can use the same worksheet/prange etc for subsequent pivots you want to add to the code

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-13-2015
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    3

    Re: Macro to Create Pivots

    That's so amazing! Thank you very very much! I'm going to go work on figuring out the rest. You're awesome!

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Macro to Create Pivots

    not a problem

    if you come along to any speed humps come back come back to site for help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need to create list which pivots from 2 source files
    By shanksy in forum Excel General
    Replies: 1
    Last Post: 08-15-2016, 07:15 AM
  2. Replies: 1
    Last Post: 04-29-2015, 11:27 AM
  3. Macro to create Pivots
    By Japi_s in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-16-2013, 04:40 PM
  4. Help with Pivots to create a Pareto Chart
    By Nikhil Raj in forum Excel General
    Replies: 0
    Last Post: 07-26-2012, 10:13 AM
  5. Macro to create two pivots on the same page
    By Ivor in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-07-2011, 03:50 AM
  6. How do I create multiple worksheets from Page Pivots
    By Vinay in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2006, 12:10 PM
  7. [SOLVED] I would like to build macro's to do pivots or part of pivots
    By Todd F. in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-29-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1