Untested...

...., tabledestination:=worksheets("Forecast").range("A1 "), ...

Aaron wrote:

Hello,

I have the following code that I need to change. I need the source data to
check for the end of page and the Table Destination to be on a different tab
(TabName: forecast)

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Forecasting Tool'!R2C1:R4784C44").CreatePivotTable
TableDestination:=Range( _
"A1"), TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Unique Ref1"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Wk1 Forecast by
Kit")
.Orientation = xlDataField
.Caption = "Max of Wk1 Forecast by Kit"
.Function = xlMax
End With
End Sub

Thanks!

--

Dave Peterson

Use a dynamic name that grows/contracts with the data.

Check how Debra Dalgleish does it:
http://contextures.com/xlNames01.html#Dynamic

Aaron wrote:

Hi Dave,
This piece worked but also needed to know how to extend the source data as
new records are added.

Thanks,
Aaron

"Dave Peterson" wrote:

Untested...

...., tabledestination:=worksheets("Forecast").range("A1 "), ...

Aaron wrote:

Hello,

I have the following code that I need to change. I need the source data to
check for the end of page and the Table Destination to be on a different tab
(TabName: forecast)

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Forecasting Tool'!R2C1:R4784C44").CreatePivotTable
TableDestination:=Range( _
"A1"), TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Unique Ref1"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Wk1 Forecast by
Kit")
.Orientation = xlDataField
.Caption = "Max of Wk1 Forecast by Kit"
.Function = xlMax
End With
End Sub

Thanks!

--

Dave Peterson


--

Dave Peterson