Hi,
I am doing an assignment for school and have run into some
problems. Firstly we have to write a macro to filter data
from a spreadsheet. Then according to the data make a
pivot table. The code generated by recording a macro works
if the number of rows are the same but because of the
filter function this is not the case.
I am wondering if someone could help be change or access
the rows through programming. The code is currently:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Temp!R1C1:R262C13").CreatePivotTable
TableDestination:=Range("A264"), _
TableName:="PivotTable2"
and it is the Sorcedata:="Temp!R1C1:R262:C13" that is
causing the headaches
Thanks

Sophie: try one of these

[sheet!address] is a short way of coding a range
currentregion is like pressing ctrl* to expand a range
until range is bordered by empty cells.

if the Pivot already exitst in a264 you'll have to clear it first
otherwise the code will generate an error.


Sub mkPiv()
ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=[temp!a1].CurrentRegion).CreatePivotTable _
TableDestination:=[a264], TableName:="PivotTable2"
End Sub

Sub mkPiv2()
ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Range([temp!a1], [temp!a65536].End(xlUp)).Resize(, _
2)).CreatePivotTable _
TableDestination:=[a264], TableName:="PivotTable2"
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Sophie" wrote:

Hi,
I am doing an assignment for school and have run into some
problems. Firstly we have to write a macro to filter data
from a spreadsheet. Then according to the data make a
pivot table. The code generated by recording a macro works
if the number of rows are the same but because of the
filter function this is not the case.
I am wondering if someone could help be change or access
the rows through programming. The code is currently:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Temp!R1C1:R262C13").CreatePivotTable
TableDestination:=Range("A264"), _
TableName:="PivotTable2"
and it is the Sorcedata:="Temp!R1C1:R262:C13" that is
causing the headaches
Thanks

typo!
resize s/b 13

Sub mkPiv2()
ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Range([temp!a1], [temp!a65536].End(xlUp)). _
Resize(,13)).CreatePivotTable _
TableDestination:=[a264], TableName:="PivotTable2"
End Sub

cheerz!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


keepitcool wrote:

Sub mkPiv2()
ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Range([temp!a1], [temp!a65536].End(xlUp)).Resize(, _
13)).CreatePivotTable _
TableDestination:=[a264], TableName:="PivotTable2"
End Sub