You can use the current region of the first cell on Sheet1, e.g.:

Dim rngData As Range
Set rngData = Worksheets("Sheet1").Range("A1").CurrentRegion

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=rngData).CreatePivotTable TableDestination:="", _
TableName:="PivotTable1"


JT wrote:
I have a master file that contains the data for every cost center. I need to
create a macro that will split the data into separate files for each cost
center. That hasn't been a problem and I have that working fine. All of the
data is being pasted on Sheet 2

What I need to do is create a pivot table on Sheet 1 with the data on Sheet
2. The rows of data will vary on Sheet 2 depending on the cost center. One
sheet may have 125 rows while another may have 550.

I'm splitting the data within a loop and want to create the pivot table each
time I copy and paste the data for each cost center.

I'm guessing I will have to create a range on each sheet 2 to refer to the
data in the pivot table code. I think I can do that.

(1) Can I use the same range name for each pivot table? And how do I delete
the range name after I create the pivot table

(2) How do I refer to the data range within the pivot table code?

Thanks for the help.........


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html