Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

New source data for pivot table

Status
Not open for further replies.

NicolaasH

Technical User
Sep 11, 2007
38
Hi,

I have a pivot table that is based on a list in a different workbook. Every month this workbook changes its name (new version) and the list changes in length. Since the source workbook changes, using a basic dynamic range is not an option.

I therefore have to redefine the source data of the pivot table. I now have a procedure where through a GetOpenfilename the new workbook is opened by the user and the list is in a standard worksheet, so easily found. Also determining the list is not a problem.

Now I need to redefine the sourcedata, but I cannot seem to get that done. I have tried using the range of the list (address) or the ListObject itself in different ways, but can't seem to get the syntax right:

Code:
Set objList1 = OpenWorksheet.ListObjects(1)
Set Pivot1Sheet = ThisWorkbook.Worksheets("Pivot1")
Set DistributorPivotTable1 = Pivot1Sheet.PivotTables("DistributorPivotTable1")

'either determine the range of the listobject and then something like:
DistributorPivotTable1.PivotTableWizard.Add Sourcedata:=DistributorPivotTable1Range)

'or use the listobject directly, something like:
DistributorPivotTable1.PivotTableWizard SourceData:=objList1

How do I assign the new range or list to the pivot table?

Thanks,
Nick
 




Hi,

Use your macro recorder.

In the PT. activate the PT Wizard.

Go BACK until you get to GetData.

Drill down to the new workbook.



Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![tongue][/sub]
 
Skip,

I have tried that, however, I do not seem to get it translated to usable code.
The wizard returns the following:

Code:
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        "'[WW Pipeline Axon January 2008 V5.xls]Pipeline'!R9C1:R297C52"

However if I then change the code:

Code:
Public Sub UpdatePivotTables([URL unfurl="true"]WWWorkbook[/URL] As Workbook)

Dim PipelineWorksheet As Worksheet, Pivot1Sheet As Worksheet
Dim objList1 As ListObject
Dim DistributorPivotTable1 As PivotTable
Dim DistributorPivotTable1Range As Variant

Set PipelineWorksheet = [URL unfurl="true"]WWWorkbook.Worksheets("Pipeline")[/URL]
Set objList1 = PipelineWorksheet.ListObjects(1)
Set Pivot1Sheet = ThisWorkbook.Worksheets("Pivot1")
Set DistributorPivotTable1 = Pivot1Sheet.PivotTables("DistributorPivotTable1")

DistributorPivotTable1Range = objList1.Range.Address(, , , True)
DistributorPivotTable1.PivotTableWizard SourceType:=xlDatabase, SourceData:=DistributorPivotTable1Range

End Sub
This returns a Run-time error '1004' ; The pivot table field name is not valid....

This error is rediculous, since the list has fully unique headers and I have already established that I can build a pivot table with it.
DistributorPivotTable1Range returns the right range: '[WW Pipeline Axon January 2008 V5.xls]Pipeline'!$A$9:$AZ$297, which is the exact range of the list.

What may be neccesary to know is that the worksheet "Pivot1" contains more pivot tables next to the one I am trying to change.

What have I done wrong?

Nick
 



What PivotTables
Code:
dim pvt as pivottable
for each pvt in activesheet.pivottables
   msgbox pvt.name & ":" & pvt.tablerange1
next


Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![tongue][/sub]
 
Skip,
I don't get it yet, but thanks anyway.
I have disgarded the whole idea and solved it differently (I now put everything in an array and with that make the grouping and comparison).
Nick
 



The code that I posted would tell you WHAT PT's are on the sheet and WHERE.

Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top