dcsimg
Results 1 to 2 of 2

Thread: Update PivotTable Source

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    10

    Update PivotTable Source

    I have many differents Worksheets with 1 or more pivot tables, their source is always a worksheet.

    For example, the source of a PivotTable is "sheet_data1!$A$1:$I$1149". The worksheet "sheet_data1" has 1149 rows. I have a program that changed this worksheet and it could increase or decrease the number of rows in that sheet.

    So the pivot will not calculate certain rows if there are more rows tha before or consider empty rows when there are less rows than before.

    I wish to update the source with right number of rows for each pivot table in the woorkbook. Is there a way to do this in this code?

    Edit:
    After the links given a changing a few things,it worked. Here is the code:
    Code:
    Private Sub Workbook_Open()
        For Each St In ActiveWorkbook.Worksheets
            For Each pt In St.PivotTables
                    Dim StartPoint As Range
                    Dim NewRange As String
                    Dim LastCol As Long
                    Dim lastRow As Long
                    Dim Data_Sheet As Worksheet
                    Dim DataRange As Range
    
                    Set Data_Sheet = ThisWorkbook.Worksheets(Split(pt.PivotCache.SourceData, "!")(0))
                    Set StartPoint = Data_Sheet.Range("A1")
                    LastCol = StartPoint.End(xlToRight).Column
                    DownCell = StartPoint.End(xlDown).Row
                    
                    Set DataRange = Data_Sheet.Range(StartPoint, Data_Sheet.Cells(DownCell, LastCol))
                    NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)
    
                    pt.ChangePivotCache ActiveWorkbook. _
                    PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)
            Next
        Next
    End Sub
    Last edited by kadzu; Jun 14th, 2018 at 07:34 AM.

  2. #2
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,176

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
  •  



Featured


Click Here to Expand Forum to Full Width