How do I make source data for pivot variable.

  • Thread starter Thread starter Mercury
  • Start date Start date
M

Mercury

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
 
Go to Insert, Name, Define

Name: Cells
RefersTo: =Offset(SheetName!$A$1,0,0,CountA(SheetName!$A:$A),10)

Replace the $A$1 with appropriate starting cell, and column $A:$A with relevant
column that always contains data.

Replace 10 with how many columns your data spans.

Then when setting up your pivot table instead of selecting the range of data
type in "Cells" (which is the named range referred to by above procedure).

This will now ensure that whenever a new row is added or deleted from your list
of data it will always update.

Ensure Pivot table options are also set to refresh on open.

Cheers, Al.
 
Back
Top