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:=xlDatab ase,
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" wrote in message
...
Thanks for this, please be more specific, im lost...

"Roger Govier" wrote:

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" wrote in message
...
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:=xlDatab ase,
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





__________ 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