Results 1 to 4 of 4

Thread: Excel VBA Macro To Dynamically Change Source for Pivot Table

  1. #1
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13

    Excel VBA Macro To Dynamically Change Source for Pivot Table

    I have a spreadheet with a sheet named "Pivot table" and a sheet named "Import" Data"

    I use a macro to import the data and to total and age the data on the sheet "Import Data" The macro works perfectly

    I need a macro that will change the Pivot Table Data Size from A1 to where the last value appears in Column L. However, text "Total WIP" appears in Col G three rows after the last value in Col G and the total value appears three columns to the right of this

    I have attached a sample


    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Code:
        Worksheets("Pivot Table").PivotTables("PivotTable4").ChangePivotCache _
        ActiveWorkbook.PivotCaches.Create _
            (SourceType:=xlDatabase, SourceData:=Worksheets("Imported Data").Range("A1:L" & _
            Worksheets("Imported Data").Cells(Rows.Count, 1).End(xlUp).Row).Address(External:=True), _
            Version:=xlPivotTableVersion14)
    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. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Thanks for the help, much appreciated

Similar Threads

  1. Replies: 1
    Last Post: 05-03-2013, 04:41 PM
  2. Replies: 2
    Last Post: 04-26-2013, 04:59 PM
  3. Replace Pivot Table Source Range with New Address VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 04-25-2013, 07:51 PM
  4. Create a Pivot table
    By NITIN SHETTY in forum Excel Help
    Replies: 3
    Last Post: 01-26-2013, 11:01 AM
  5. Lookup and Count Using Pivot Table
    By RobExcel in forum Excel Help
    Replies: 2
    Last Post: 12-21-2012, 11:08 AM

Posting Permissions

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