+ Reply to Thread
Results 1 to 5 of 5

Automatic update of pivot table with error - EXCEL

  1. #1
    Registered User
    Join Date
    02-15-2019
    Location
    Brasil
    MS-Off Ver
    365
    Posts
    3

    Automatic update of pivot table with error - EXCEL

    I find a error in bellow code in depurator the VBA point this:
    Pivot_Sheet.PivotTables(PivotName). _ ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)
    The code below:




    Sub UpdatePivotTableRange()

    Dim Data_Sheet As Worksheet
    Dim Pivot_Sheet As Worksheet
    Dim StartPoint As Range
    Dim DataRange As Range
    Dim PivotName As String
    Dim NewRange As String
    Dim LastCol As Long
    Dim lastRow As Long
    'Set Pivot Table & Source Worksheet
    Set Data_Sheet = ThisWorkbook.Worksheets("BASE")
    Set Pivot_Sheet = ThisWorkbook.Worksheets("TABELA")
    'Enter in Pivot Table Name
    PivotName = "Tabela dināmica3"
    'Defining Staring Point & Dynamic Range
    Data_Sheet.Activate
    Set StartPoint = Data_Sheet.Range("A1")
    LastCol = StartPoint.End(xlToRight).Column
    DownCell = StartPoint.End(xlDown).Row
    Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
    NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)
    'Change Pivot Table Data Source Range Address
    Pivot_Sheet.PivotTables(PivotName). _
    ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)
    'Ensure Pivot Table is Refreshed
    Pivot_Sheet.PivotTables(PivotName).RefreshTable
    'Complete Message
    Pivot_Sheet.Activate
    MsgBox "Relatório " & PivotName & " foi atualizado. Aperte OK."

    End Sub
    erro.jpg

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,336

    Re: Automatic update of pivot table with error - EXCEL

    Hello and welcome to the forum.
    Please upload the workbook. Pictures are rarely any use.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    02-15-2019
    Location
    Brasil
    MS-Off Ver
    365
    Posts
    3

    Re: Automatic update of pivot table with error - EXCEL

    Attachment 711511

    here the file

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,336

    Re: Automatic update of pivot table with error - EXCEL

    Why not create a dynamic range name for your data, say "MyPTDate"

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and then just use

    Planhila1.PivotTables("Tabela dināmica3").PivotCache.Refresh

  5. #5
    Registered User
    Join Date
    02-15-2019
    Location
    Brasil
    MS-Off Ver
    365
    Posts
    3

    Re: Automatic update of pivot table with error - EXCEL

    Thanks for your reply!!
    I“m newbie in vba....which lines I must change to code works?

    "Why not create a dynamic range name for your data, say "MyPTDate""
    Do you reffer to this line: "PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)"


    Where I put this?
    "=OFFSET(BASE!$A$1,0,0,COUNTA(BASE!$A:$A),COUNTA(BASE!$1:$1))"

    Thanks a lot for your help!!!!





    =OFFSET(BASE!$A$1,0,0,COUNTA(BASE!$A:$A),COUNTA(BASE!$1:$1))

+ 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. Automatic update of date criteria in excel formula pulled from a Pivot table
    By Jessica at Heritage in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-15-2019, 01:14 PM
  2. Linking Excel Table to Word and automatic or manual update
    By shriexcelforum in forum Excel General
    Replies: 1
    Last Post: 09-16-2015, 02:07 PM
  3. Automatic summary table update not using pivot chart
    By kheino1111 in forum Excel General
    Replies: 1
    Last Post: 04-01-2014, 12:34 AM
  4. Pivot Table Update Error
    By cp41 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-22-2013, 01:11 PM
  5. VBA Pivot Table Update Run Time Error
    By DoodlesMama in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2013, 02:58 PM
  6. Automatic Pivot Update?
    By maxashton in forum Excel General
    Replies: 0
    Last Post: 12-04-2012, 06:20 AM
  7. Automatic update of Pivot Table based on filtered data
    By humacdeep in forum Excel General
    Replies: 4
    Last Post: 02-08-2012, 06:43 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