Solved: Update Pivot Data Range through Visual Basic

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 
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.
 

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.
 
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)
 
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.
 

Attachments

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).
 

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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top