Pivottable 
Author Message
 Pivottable

I want to create a pivottable by use of a macro and the size of the data
(the number of rows) to pivot will vary from day to day. I probably need to
do something like search for the 1st blank cell and then select all of the
data above the cell to pivottable, but wouldn't have a clue how to do this
because of my inexperience.

Any help would be appreciated.

Many thanks.....

Shane Clark



Thu, 08 Jul 2004 17:07:54 GMT  
 Pivottable
When you record the steps in creating a pivot table, Excel inserts a
specific range of cells into the code. For example,:

  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
      "Sheet1!R1C1:R462C6").CreatePivotTable TableDestination:="", _
      TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

You can replace the SourceData string with something more flexible.
You'll always have the same starting cell, so you can use its
CurrentRegion to define the region that you want included in the pivot
table.

  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
      Range("A1").CurrentRegion).CreatePivotTable TableDestination:="", _
      TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

Quote:

> I want to create a pivottable by use of a macro and the size of the data
> (the number of rows) to pivot will vary from day to day. I probably need to
> do something like search for the 1st blank cell and then select all of the
> data above the cell to pivottable, but wouldn't have a clue how to do this
> because of my inexperience.

> Any help would be appreciated.

> Many thanks.....

> Shane Clark



Thu, 08 Jul 2004 19:52:06 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Pivottable.Laguage & Pivottable.Lang properties

2. Totals different in OWC PivotTable than in Excel PivotTable

3. Drilldown functionality to Pivottable when Pivottable commandtext is a XML file.

4. Make a pivottable from > 1 other pivottable in xl97?

5. Anyone use OWC PivotTable

6. Pivottable default formatting

7. PivotTable: Two data fields same row

8. How to set pivottable SQL

9. Event Pivottable

10. How can I detect if no data in PivotTable after setting a CurrentPage value


 
Powered by phpBB® Forum Software © phpBB Group