VBA Pivot Tables with unkown # of rows in the data set
 

I am trying to create a macro that creates a pivot table. Problem is
the # of rows of data is always variable (# of columns is always
constant).

Does the range of the Pivot table have to be explicitly defined, as
below:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Data'!*R1C1:R2500C42*",

Or can the RC:RC reference be variable, and depend on the # of rows?

I have tried concatenating the RC:RC reference, such as:

"'Data'!R1C1:R" & x & "C42",
where x = rowcount(Columns("A:A")) (rowcount is a UDF that counts the #
of rows of data).

But that doesn't work. Any ideas anyone?


---
Message posted from http://www.ExcelForum.com/

VBA Pivot Tables with unkown # of rows in the data set
 

You could use a dynamic formula to name the source range, e.g. Database.
Then, use the range name in the code --

SourceData:="Database"

Tbone_Scott < wrote:
I am trying to create a macro that creates a pivot table. Problem is
the # of rows of data is always variable (# of columns is always
constant).

Does the range of the Pivot table have to be explicitly defined, as
below:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Data'!*R1C1:R2500C42*",

Or can the RC:RC reference be variable, and depend on the # of rows?

I have tried concatenating the RC:RC reference, such as:

"'Data'!R1C1:R" & x & "C42",
where x = rowcount(Columns("A:A")) (rowcount is a UDF that counts the #
of rows of data).

But that doesn't work. Any ideas anyone?


---
Message posted from http://www.ExcelForum.com/



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html