I am trying to create a macro that I can use with various data sets. The
only difference is the number of rows of data. I have created the following
macor yet don't know what to put in place of the R4000 (as the number of rows
varies for each data set. It could contain anywhere from 2 rows to 15000).
I have gathered through searching here that I may have to use a variable? but
am really unsure how to write or set up the code. I have seen discussions on
how to make a pivot adapt to a dynamic data set by giving the data set a name
but this is not my case. My macro must be able to run my pivot on many
distinct worksheet datasets that only vary in length. Once the dataset is
populated for that particular worksheet it is never changed - not dynamic.

The macro must run the pivot table from data contained in column A1:A35 and
row 1 thru UNKNOWN
I would like to base the row lenght off of Column A's last populated cell.
The pivot table will never change in layout but must adapt to the length of
the rows of any given worksheet dataset.

This is what I have thusfar. I need to replace the R4000 with some kind of
variable I guess.

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

I saw this link but you still have to manually select the pivot table data:
http://www.contextures.com/xlPivot01.html I need the macro to be able to
figure out the data set.

I figure it may involve several steps to get this accomplished and that is
fine if necessary as hopefully it all can be incorporated into one macro.

Any help here would be greatly appreciated.

It should be something close to this...

dim rng as range

with sheets("Sheet1")
set rng = .Range(.Range("A1"), .Cells(Rows.count, 35).end(xlUp))
end with
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
rng.parent.name & "!" $ rng.Address).CreatePivotTable
TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

--
HTH...

Jim Thomlinson


"rsulliva" wrote:

I am trying to create a macro that I can use with various data sets. The
only difference is the number of rows of data. I have created the following
macor yet don't know what to put in place of the R4000 (as the number of rows
varies for each data set. It could contain anywhere from 2 rows to 15000).
I have gathered through searching here that I may have to use a variable? but
am really unsure how to write or set up the code. I have seen discussions on
how to make a pivot adapt to a dynamic data set by giving the data set a name
but this is not my case. My macro must be able to run my pivot on many
distinct worksheet datasets that only vary in length. Once the dataset is
populated for that particular worksheet it is never changed - not dynamic.

The macro must run the pivot table from data contained in column A1:A35 and
row 1 thru UNKNOWN
I would like to base the row lenght off of Column A's last populated cell.
The pivot table will never change in layout but must adapt to the length of
the rows of any given worksheet dataset.

This is what I have thusfar. I need to replace the R4000 with some kind of
variable I guess.

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

I saw this link but you still have to manually select the pivot table data:
http://www.contextures.com/xlPivot01.html I need the macro to be able to
figure out the data set.

I figure it may involve several steps to get this accomplished and that is
fine if necessary as hopefully it all can be incorporated into one macro.

Any help here would be greatly appreciated.