Could someone point me in the right direction or give me a reference on
automating pivot table creation.

I have a pivot table created but the information it is based on changes
on a daily basis. What I had done was set up a macro that pulls the
new list data into my workbook and replaces the data that the pivot
table is based on, then refreshes the table. That way I don't have
to rebuild the table each time I have to update the list. The problem
is that the dropdowns on the pivot table still reflect the old data
values along with the new ones.

Is it possible to get around this and reset the table completely
without having to recreate the table? Or is there a way to automate the
creation of the table?

Thanks
Steve

There's information here on clearing old items in a pivot table:

http://www.contextures.com/xlPivot04.html

Smonczka wrote:
Could someone point me in the right direction or give me a reference on
automating pivot table creation.

I have a pivot table created but the information it is based on changes
on a daily basis. What I had done was set up a macro that pulls the
new list data into my workbook and replaces the data that the pivot
table is based on, then refreshes the table. That way I don't have
to rebuild the table each time I have to update the list. The problem
is that the dropdowns on the pivot table still reflect the old data
values along with the new ones.

Is it possible to get around this and reset the table completely
without having to recreate the table? Or is there a way to automate the
creation of the table?

Thanks
Steve



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

You would either need to delete the unused values or create a new table. To
create a new table, turn on the macro recorder while you create one
manually. Turn off the macro recorder after completion.

http://www.contextures.com/xlPivot04.html

for code on clearing out the unused data.

Go to http://www.contextures.com/tiptech.html

and look under P for pivot tables for other information on Pivot Tables.
(Debra Dalgleish's site).

--
Regards,
Tom Ogilvy


"Smonczka" wrote in message
oups.com...
Could someone point me in the right direction or give me a reference on
automating pivot table creation.

I have a pivot table created but the information it is based on changes
on a daily basis. What I had done was set up a macro that pulls the
new list data into my workbook and replaces the data that the pivot
table is based on, then refreshes the table. That way I don't have
to rebuild the table each time I have to update the list. The problem
is that the dropdowns on the pivot table still reflect the old data
values along with the new ones.

Is it possible to get around this and reset the table completely
without having to recreate the table? Or is there a way to automate the
creation of the table?

Thanks
Steve

Thank you both for your help.

I decided to just rebuild the table on the fly using a macro. I
created a new table but when I run the macro it errors out ...

'make pivot table
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"TableData").CreatePivotTable TableDestination:= _
"[Pivot_Report.xls]TABLE!R3C2",
TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion10

This line returns an error.
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Week", _
"Assigned to +", "Site Name +", "Cause Code", "Data")

Would you know why this would be? I should just be running the exact
same steps as I did when I made the macro.

Thanks again,
Steve

The obvious conclusion would be that at least one of those fields does not
exist or that adding it as a rowfield causes a problem. Using the data that
causes the problem as your source, try again recording while you add the
table manually and see if you can discover the problem.

--
Regards,
Tom Ogilvy


"Smonczka" wrote in message
ups.com...
I decided to just rebuild the table on the fly using a macro. I
created a new table but when I run the macro it errors out ...

'make pivot table
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"TableData").CreatePivotTable TableDestination:= _
"[Pivot_Report.xls]TABLE!R3C2",
TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion10

This line returns an error.
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Week", _
"Assigned to +", "Site Name +", "Cause Code", "Data")

Would you know why this would be? I should just be running the exact
same steps as I did when I made the macro.

Thanks again,
Steve

Tom actualy the problem was very strange. I had to insert a string
that activated a cell in the pivot table then continue with the macro.
It looked as if the macro would make a blank table then could not
figure out where the table was located. Once I inserted code to point
any cell in the table after the table was created but before it was
populated it worked perfectly.

I have no Idea why this was happening though.

Steve

Tom actualy the problem was very strange. I had to insert a string
that activated a cell in the pivot table then continue with the macro.
It looked as if the macro would make a blank table then could not
figure out where the table was located. Once I inserted code to point
any cell in the table after the table was created but before it was
populated it worked perfectly.

I have no Idea why this was happening though.

Steve

Tom actualy the problem was very strange. I had to insert a string
that activated a cell in the pivot table then continue with the macro.
It looked as if the macro would make a blank table then could not
figure out where the table was located. Once I inserted code to point
any cell in the table after the table was created but before it was
populated it worked perfectly.

I have no Idea why this was happening though.

Steve