Pivot table in VB

A

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?
 
R

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

ake said:
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
 
R

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

ake said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top