How do I make source data for pivot variable.

Giganews Newsgroups
Subject: How do I make source data for pivot variable.
Posted by:  Mercury (pulgar…@bellsouth.net)
Date: Sat, 4 Oct 2003

I recorded the following code  as I built a pivot table. The data for this
particular table was 3 columns wide and 300 rows down.
My problem is that the number of rows in my data is always different. One
day may be 300 another 345, or 280.
Rigth now I modify my pivot to pick up more rows than my data will ever
have, I hope, and adjust the data by adding filler rows to make it a
constant to match the rows my pivot table picks up.
How can I get  my pivot table to always scoop up exactly the number of rows
containing the data. No more rows or less, and eliminate the other adjusting
filler code.
I know how to determine the end of my data using the
selection.end(xldown).select. This puts me at the end of my data and I can
use this info  to program the pivot. But I am having toruble formatting this
info into the range of the pivot table code. How do I format the source data
info. that part like !R1C1:R300C3.

Thanks.

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        "main1!R1C1:R300C3", TableDestination:="", TableName:="PivotTable2"
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="SYSTEM", _
        ColumnFields:="CATEGORY "
    ActiveSheet.PivotTables("PivotTable2").PivotFields("COUNT
OF").Orientation = _
        xlDataField

Replies