I got an error, because when I click a button, my program clear the contents
in "Data" sheet except the title first, then fill the empty field with new
data. Using the build-in dynamic range, the range was decided before the
program filling the new data so only first row is defined, then some filed
changed on "Summary" sheet and the fake report occupies other space I need to
clear but you can not delete part of a pivot table then I got the error.

Is there a way to control the time or sequence of this build-in function so
that it can change the range after the old data are cleared and new data are
filled?

Thanks.

Will

"Jim Thomlinson" wrote:

You do not need code for this. You can use a dynamic named range. Check out
this link...

http://www.contextures.com/xlPivot01.html
and/or
http://www.cpearson.com/excel/named.htm#Dynamic
--
HTH...

Jim Thomlinson


"will-d" wrote:

Dear Experts,

I have an existing pivot table named PivotTable2 in sheet "summary" and its
source in another sheet "Data" in same workbook. "Data" always changes, The
row and columne number in "Data" are two calculated results stored in
variable lrow2 and lcol2.

I would like to keep the layout and location of PivotTable2 in "Summary"
sheet but only change the data source range in sheet "Data". I am using excel
2002 and the folowing code does not work.

Sheet("Data").Activate
Dim mysource2 as Range
With ActiveSheet
Set mysource2 = .Range(.Cells(1, 1), .Cells(lrow2, lcol2))
End With

Sheets("Summary").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=mysource2

How do I change a range of an existing Pivot Table without changing anyother
settings?

Thanks!