Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to change Pivot Table data source
I have a workbook with 20 Pivot Tables that I need to update the last row
referenced by the data set for the Pivot Table via VBA. I have the last row number by using: NumberOfEntries = _ Worksheets("Raw_Data").Cells(Rows.Count, "A").End(xlUp).Row .... (more code) and after loading updated data I want to programmatically update the data set used to include all rows in the worksheet. Problem is that Excel hard codes the path to the file name in the SourceData:= field: Sheets("Usage by PMT PT").Select ActiveSheet.PivotTables("PivotTable15").ChangePivo tCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:="C:\[MyWorkbook.xlsm]Organization!R5C1:R860C17", _ Version:=xlPivotTableVersion12) I've tried using ActiveWorkbook.Path and building a path string to where ever the workbook might be but that doesn't seem to work. All I need to do is update the total number of rows involved in the data set as the columns don't change. Any ideas on how to do this? I searched the discussion group already and found nothing pertaining to this type of problem. Also, I am using 2007 with latest service packs installed. Thanks, Bryan44 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table change data source | Excel Discussion (Misc queries) | |||
Can't change Pivot Table data source | Excel Worksheet Functions | |||
Change pivot table source data via macro | Excel Discussion (Misc queries) | |||
change data source of pivot table | Excel Discussion (Misc queries) | |||
Change the range of a pivot table data source | Excel Programming |