I've got a macro which creates a Pivot Table from a data extract. Because
the data extract varies in size from time to time I've set the Pivot Table
range to 25000 rows.

Everything is fine except:

1) I'm left with "Blanks" as a Heading for each of the fields in the Pivot
table. Whilst I can manually hide these I haven't been able to change the
code so this happens automatically

2) The data extract includes data in currency format which switches to
"General" and data in mmm-yyyy format which switches to dd/mm/yyyy format. I
could write seperate code to select a range and format it after the Pivot
Table has run but I'm nervous about this in case the Pivot Table area changes.

I've added my code below and would really appreciate some help with how to
overcome the two issues above.

Many thanks

-----------------------

Sheets("Summary").Select
Range("A3").Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Extract!R1C1:R25000C10").CreatePivotTable
TableDestination:=Range("A3"), _
TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"Cost Centre", "Ledger Code"), ColumnFields:="Month"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total")
.Orientation = xlDataField
.Caption = "Sum of Total"
.Function = xlSum
End With
Application.CommandBars("PivotTable").Visible = False
End Sub

1. Define a dynamic range name which includes only the rows with data,
something like this:

Name: PTData
Refers To:
=OFFSET(Extract!$A$1,0,0,COUNTA(Extract!$A:$A),COU NTA(Extract!$1:$1))

Then change your code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Extract!PTData").CreatePivotTable

2. Apply the number format to the pivot field:

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Whatever")
.NumberFormat = "mmm-yyyy"
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"nospaminlich" wrote in message
...
I've got a macro which creates a Pivot Table from a data extract. Because
the data extract varies in size from time to time I've set the Pivot Table
range to 25000 rows.

Everything is fine except:

1) I'm left with "Blanks" as a Heading for each of the fields in the Pivot
table. Whilst I can manually hide these I haven't been able to change the
code so this happens automatically

2) The data extract includes data in currency format which switches to
"General" and data in mmm-yyyy format which switches to dd/mm/yyyy format.
I
could write seperate code to select a range and format it after the Pivot
Table has run but I'm nervous about this in case the Pivot Table area
changes.

I've added my code below and would really appreciate some help with how to
overcome the two issues above.

Many thanks

-----------------------

Sheets("Summary").Select
Range("A3").Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Extract!R1C1:R25000C10").CreatePivotTable
TableDestination:=Range("A3"), _
TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array( _
"Cost Centre", "Ledger Code"), ColumnFields:="Month"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total")
.Orientation = xlDataField
.Caption = "Sum of Total"
.Function = xlSum
End With
Application.CommandBars("PivotTable").Visible = False
End Sub

Thank you very much indeed Jon - it works a treat.

Thanks again