Pivot Tables

GeorgeB · Mar 17th, 2002, 06:44 PM

Greetings all
I have a procedure that creates a pivot table. When new data is added the procedure deletes the old table and creates a new one that includes the new data. The sheet number is incremented up each time IE.
Sheet(148) Any way to turn back the clock on this? I have tried renaming the sheet in the properties window but the next time the procedure runs it's back up to what it was plus one.

SamS · Mar 17th, 2002, 08:37 PM

Why not change your procedure to include RefreshTable when new data is added eg

ActiveSheet.PivotTables("PivotTable2").RefreshTable

GeorgeB · Mar 17th, 2002, 08:44 PM

That will work if the existing data is changed but when you add data to the bottom of the input area it doesn't see it.

SamS · Mar 17th, 2002, 09:33 PM

George - I keep the original sheet (it's renamed to something more suitable) clear only the data and use this simple routine to determine the last row in the data and substitute the variable RESP in the range for the pivot table. This way the pivot table no doesn't keep changing.

Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Select
RESP = ActiveCell.Row()

Dave Hawley · Mar 17th, 2002, 09:39 PM

Hi George

You should use a Dynamic range, this way you can add and delete from the range as needed and It will expand/contract Up/down Left/Right accordingly

I have many examples here

http://www.ozgrid.com/Excel/DynamicRanges.htm

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 17th, 2002, 06:44 PM	#1
GeorgeB Board Regular Join Date: Feb 2002 Location: Las Vegas Nevada USA Posts: 240	Greetings all I have a procedure that creates a pivot table. When new data is added the procedure deletes the old table and creates a new one that includes the new data. The sheet number is incremented up each time IE. Sheet(148) Any way to turn back the clock on this? I have tried renaming the sheet in the properties window but the next time the procedure runs it's back up to what it was plus one.

Mar 17th, 2002, 08:37 PM	#2
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	Why not change your procedure to include RefreshTable when new data is added eg ActiveSheet.PivotTables("PivotTable2").RefreshTable

Mar 17th, 2002, 08:44 PM	#3
GeorgeB Board Regular Join Date: Feb 2002 Location: Las Vegas Nevada USA Posts: 240	That will work if the existing data is changed but when you add data to the bottom of the input area it doesn't see it.

Mar 17th, 2002, 09:33 PM	#4
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	George - I keep the original sheet (it's renamed to something more suitable) clear only the data and use this simple routine to determine the last row in the data and substitute the variable RESP in the range for the pivot table. This way the pivot table no doesn't keep changing. Range("A2").Select Selection.End(xlDown).Select ActiveCell.Select RESP = ActiveCell.Row()

Mar 17th, 2002, 09:39 PM	#5
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi George You should use a Dynamic range, this way you can add and delete from the range as needed and It will expand/contract Up/down Left/Right accordingly I have many examples here http://www.ozgrid.com/Excel/DynamicRanges.htm