Results 1 to 3 of 3

Thread: Update Pivot Table Source Data With Dynamic Range Of Non Empty Cells

  1. #1

    Update Pivot Table Source Data With Dynamic Range Of Non Empty Cells

    Hello,

    Please please please could someone smarter than me have a look at this. I have been working on this all day and feel like crying. I have two sheets on the same workbook. One ("Data") holds the info for the Pivot table source data. The other tab ("Shipment Timeliness Report") has the pivot table on.

    I have written the following code to try and set the data source range from one sheet (down to the last cell with data in), then update the pivot table with this range on the second sheet.

    Code:
    With ShipmentTimelinessWeekPrevious.Sheets("Data").Select
    Dim NewPTData As Range
    Set NewPTData = Range(Range("A4"), Range("A4").End(xlDown))
        NewPTData.Resize(, NewPTData.Columns.Count + 10).Select
        End With
        
    With ActiveWorkbook.Worksheets("Shipment Timeliness Report").Select
    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            Sheets("Data").Selection _
            , Version:=xlPivotTableVersion10)
            Sheets("Shipment Timeliness Report").PivotTables.Refresh
    End With
    Anyone who can offer any help would be loved by me for ever

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Try this

    Code:
        
     strMsg = NewPTData.Resize(, NewPTData.Columns.Count + 10).Address(, , xlR1C1, True)
    
        ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:=strMsg _
            , Version:=xlPivotTableVersion12)
    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
    You're a wonderful wonderful person thanks so much, works at long last.
    full code,
    Code:
    With ShipmentTimelinessWeekPrevious.Sheets("Data").Select
    Dim NewPTData As Range
    Dim strMsg As String
    Set NewPTData = Range(Range("A4"), Range("A4").End(xlDown))
        End With
        
    With ActiveWorkbook.Worksheets("Shipment Timeliness Report").Select
    strMsg = NewPTData.Resize(, NewPTData.Columns.Count + 10).Address(, , xlR1C1, True)
    
        ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:=strMsg _
            , Version:=xlPivotTableVersion12)
    End With

Similar Threads

  1. Use SUMIFS on pivot table data
    By malta1 in forum Excel Help
    Replies: 1
    Last Post: 02-18-2014, 10:55 AM
  2. Change Pivot Table Data Source Using A Drop Down List
    By hanishgautam in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 07-05-2013, 09:33 AM
  3. Replies: 13
    Last Post: 06-10-2013, 09:05 AM
  4. 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
  5. Replies: 3
    Last Post: 03-05-2013, 03:57 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
  •