1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Update Pivot Data Range through Visual Basic

Discussion in 'Business Applications' started by WAJ0606, Mar 26, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
    I need a script that will update the range of a pivot table. A dynamic named range will not work because I will be replacing the worksheet that the range would refer to which causes an error. Right now I have:

    Private Sub UpdatePivot()
    Dim lrow As Long
    Dim pvt As PivotTable

    lrow = Sheets("OngoingIR_ER").Range("A65535").End(xlUp).Row
    Set pvt = ActiveSheet.PivotTables(1)
    pvt.SourceData = Range("A1:AS" & lrow).CurrentRegion.Address(True, True, xlR1C1, True)

    End Sub


    I get an error that my range must refer to two or more rows of data. Any ideas here?
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Can you give any more info on "replacing the worksheet"?

    You might be able to assign a variable to the replaced (?added?) sheet name and then use that, rather than referring to sheet names explicitly.

    NewSheetName = ActiveSheet.Name
    lrow = Sheets(NewSheetName).Range("A"&Rows.Count).End(xlUp).Row


    , for example.
     
  3. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
    Replace meaning written over when I output the updated data from an Access table and it replaces the previous days worksheet. I know I could link directly to the data, but the document is going on the web, which would likely create problems.

    The dynamic named range I was referring to is outside of VB (Insert>>Name>>Define). Using something like "=OFFSET(OngoingIR_ER!$A$1,0,0,COUNTA(OngoingIR_ER!$A:$A),COUNTA(OngoingIR_ER!$1:$1))" i could have the pivot's range refer to the named range if data were being added rather than replaced. I'm thinking about outputting onto a separate worksheet, copying, pasting over on worksheet with named range and deleting the copy.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    OK.

    Do you get the error if you use:

    pvt.SourceData = Sheets("OngoingIR_ER").Range("A1:AS" & lrow).CurrentRegion.Address(True, True, xlR1C1, True)

    ? (i.e. maybe OngoingIR_ER isn't the active sheet at the point that line's running)
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Update -- see the attached.

    Obviously the data on OngoingIR_ER is vastly cut down, but add another "record" (team d = 5), then switch to Sheet1, then run the sub.

    Updates OK for me.
     

    Attached Files:

  6. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
    Thanks bomb, that has it. I'll post some updated code after I implement it later today. The updated code will go through and run the macro on all worksheets in the workbook except "OngoingIR_ER" as it does not contain a pivot table (only data).
     
  7. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
    FINAL CODE

    Dim sht As Worksheet
    Dim lrow As Long
    Dim pvt As PivotTable

    Set acSheet = ActiveSheet

    For Each sht In Worksheets
    If sht.Name <> "WorksheetwithData" Then

    sht.Activate
    lrow = Sheets("WorkSheetwithData").Range("A" & Rows.Count).End(xlUp).Row
    Set pvt = ActiveSheet.PivotTables(1)
    pvt.SourceData = Sheets("OpticalData").Range("A1:B" & lrow).CurrentRegion.Address(True, True, xlR1C1, True)
    End If
    Next
    Sheets(1).Activate
    ActiveSheet.Range("A1").Select
    ActiveWorkbook.RefreshAll
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/697207

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice