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

Thank you Jim.

"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!