I am trying to make the end of my pivot table range the last cell in the
worksheet (actually the next to last cell but I can't get to the last cell
yet). Anyone know the syntax for ths? Here's my current failed experiment,
substituting a range for the hard coded R1C1:R2000C41:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Cost Data'!Range(Selection,
ActiveCell.SpecialCells(xlLastCell))").CreatePivot Table TableDestination:="",
_

TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("PLINE")
.Orientation = xlRowField
.Position = 1
End With

Any thoughts are welcome.

dim mySource as range
with worksheets("cost data")
set mysource = .range("A1:E" & .cells(.rows.count,"A").row)
end with

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:= mysource.address(external:=true)).createPivotTable _
TableDestination:="".....

I used column A to find the last row and went to column E. Change that to what
you need.

Alan P wrote:

I am trying to make the end of my pivot table range the last cell in the
worksheet (actually the next to last cell but I can't get to the last cell
yet). Anyone know the syntax for ths? Here's my current failed experiment,
substituting a range for the hard coded R1C1:R2000C41:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Cost Data'!Range(Selection,
ActiveCell.SpecialCells(xlLastCell))").CreatePivot Table TableDestination:="",
_

TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("PLINE")
.Orientation = xlRowField
.Position = 1
End With

Any thoughts are welcome.

--

Dave Peterson

You could also use a dynamic range name that grows/contracts with your data.
This is nice if the pivottable's raw data could change (new rows added kind of
thing).

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

Dave Peterson wrote:

dim mySource as range
with worksheets("cost data")
set mysource = .range("A1:E" & .cells(.rows.count,"A").row)
end with

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:= mysource.address(external:=true)).createPivotTable _
TableDestination:="".....

I used column A to find the last row and went to column E. Change that to what
you need.

Alan P wrote:

I am trying to make the end of my pivot table range the last cell in the
worksheet (actually the next to last cell but I can't get to the last cell
yet). Anyone know the syntax for ths? Here's my current failed experiment,
substituting a range for the hard coded R1C1:R2000C41:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Cost Data'!Range(Selection,
ActiveCell.SpecialCells(xlLastCell))").CreatePivot Table TableDestination:="",
_

TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("PLINE")
.Orientation = xlRowField
.Position = 1
End With

Any thoughts are welcome.

--

Dave Peterson

--

Dave Peterson

Dave,

Thanks, that worked nicely.

Alan

"Dave Peterson" wrote:

You could also use a dynamic range name that grows/contracts with your data.
This is nice if the pivottable's raw data could change (new rows added kind of
thing).

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

Dave Peterson wrote:

dim mySource as range
with worksheets("cost data")
set mysource = .range("A1:E" & .cells(.rows.count,"A").row)
end with

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:= mysource.address(external:=true)).createPivotTable _
TableDestination:="".....

I used column A to find the last row and went to column E. Change that to what
you need.

Alan P wrote:

I am trying to make the end of my pivot table range the last cell in the
worksheet (actually the next to last cell but I can't get to the last cell
yet). Anyone know the syntax for ths? Here's my current failed experiment,
substituting a range for the hard coded R1C1:R2000C41:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Cost Data'!Range(Selection,
ActiveCell.SpecialCells(xlLastCell))").CreatePivot Table TableDestination:="",
_

TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("PLINE")
.Orientation = xlRowField
.Position = 1
End With

Any thoughts are welcome.

--

Dave Peterson

--

Dave Peterson