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