dynamic source range change in pivot table

[will-d]

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!

 

[Jim Thomlinson]

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

 

[will-d]

Thank you. I am not sure if I have blank cells or rows in middle of my data
will this work?

Thank you Jim.

 

[will-d]

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]

You have completely lost me... Where is your data coming from.

If it is coming from an external database why not just hook the pivot table
directly to the database?

If you are impoorting it from other workbooks then do your import and when
the is complete use code something like
sheets("Sheet1").pivottables(1).Refreshtable

Otherwise I am not to sure exactly what the issue is... More details will be
required.