[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Re: Create pivot table with VB

Debra Dalgleish

12/15/2006 4:43:00 AM

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:=xlDatabase, _
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/ti...