Help with Pivot Table Code Please

[Guest]

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:=xlDatabase, 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

 

[Jon Peltier]

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),COUNTA(Extract!$1:$1))

Then change your code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, 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

 

[Guest]

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

Thanks again