New source data for pivot table

[NicolaasH]

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:

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

 

[SkipVought]

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]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[NicolaasH]

Skip,

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

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

However if I then change the 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

 

[SkipVought]

What PivotTables

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

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

 

[NicolaasH]

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

 

[SkipVought]

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

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