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:
How do I assign the new range or list to the pivot table?
Thanks,
Nick
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