Pivot table in VB

[ake]

I have the following code I tried to use to create Pivot table.

Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range("A1").CurrentRegion.Address)
Set PT =
PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"),
TableName:=" PivotTable")
With PT
..PivotFields("Month").Orientation = xlPageField
..PivotFields("Control Number").Orientation = xlColumnField
..PivotFields("Company Code").Orientation = xlRowField
..PivotFields("Tax Payments").Orientation = xlDataField
End With
End Sub

It gives me "error 1004" and highlights the line:
Set PT =
PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"),
TableName:=" PivotTable")

What have I done wrong?

 

[Roger Govier]

Hi

The problem is that after creating the new Worksheet, it is the active sheet
and the PT Cache is being set to a region which is just A1 of that sheet.

You need to activate the sheet with your source data, after creating the new
sheet and before setting the PT Cache.

--
Regards
Roger Govier

I have the following code I tried to use to create Pivot table.

Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range("A1").CurrentRegion.Address)
Set PT =
PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"),
TableName:=" PivotTable")
With PT
.PivotFields("Month").Orientation = xlPageField
.PivotFields("Control Number").Orientation = xlColumnField
.PivotFields("Company Code").Orientation = xlRowField
.PivotFields("Tax Payments").Orientation = xlDataField
End With
End Sub

It gives me "error 1004" and highlights the line:
Set PT =
PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"),
TableName:=" PivotTable")

What have I done wrong?



__________ Information from ESET Smart Security, version of virus
signature database 4245 (20090715) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4245 (20090715) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[ake]

Thanks for this, please be more specific, im lost...

 

[Roger Govier]

Hi

Assuming your data is on Sheet1

Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable
Worksheets.Add
ActiveSheet.Name = "PivotSheet"

Sheets("Sheet1").Activate

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=Range("A1").CurrentRegion.Address)
Set PT =
PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"),
TableName:=" PivotTable")


You need to make whatever the sheet with your data is, as the active sheet
before setting the PTCache with the line
Sheets("Sheet1").Activate
Change "Sheet1" to wahtever your sheet is called.
--
Regards
Roger Govier

Thanks for this, please be more specific, im lost...



__________ Information from ESET Smart Security, version of virus
signature database 4246 (20090715) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4247 (20090715) __________

The message was checked by ESET Smart Security.

http://www.eset.com