Microsoft Excel VBA Question

[Adamba]

Hiya!

I have been coding an automated system of creating Pivot tables and colouring certain lines on the report in VBA.

I have managed to Create a document with accurate pivot tables and coloured the required lines. The problem i am getting is that when the data is refreshed on the pivot table the colourings that my code put in dissapear. I have tried setting up my code to create pivot tables which "Preserve Formatting" but still any 'coded' colours are lost on refresh. if i manually add colours to the pivot they stay there even when the data has been refreshed.

Can Visual Basic on do 'Cosmetic' Changes to the pivot tables or am i missing something in the code below. The code below is just the pivot creation section. I add the colours later on in my prog.

I would greatly appreciate any help with this, as its starting to do my head in. Good ole Microsoft!


Thanks In advance!

Sorry ive tried to tidy it up a bit
Code Extract:

ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
"[" & Retailer & " " & Category & " DATA.XLS]£,U,D by Month!PivotTable1", TableDestination:="", _
TableName:="PivotTable2"
ActiveSheet.PivotTables("PivotTable2&quot.PivotFields("SubCat&quot.Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable2&quot.PivotFields("Type&quot.Subtotals = Array(
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable2&quot.PivotFields("Flavour&quot.Subtotals = Array _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable2&quot.PivotFields("Description&quot.Subtotals = _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable2&quot.PivotFields("Size&quot.Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable2&quot.AddFields RowFields:=Array("SubCat", _
"Description", "Type", "Flavour", "Size&quot, ColumnFields:="Wk Ending Date"
With ActiveSheet.PivotTables("PivotTable2&quot
.ColumnGrand = False
.RowGrand = False
.HasAutoFormat = False
.PreserveFormatting = True
End With
With ActiveSheet.PivotTables("PivotTable2&quot.PivotFields("Value&quot
.Orientation = xlDataField
.Name = "Value "
.NumberFormat = "£#,##0.00"
End With

Adam

 

[xlbo]

You must turn the "Autoformat Table" property to false and have enableselection = TRUE
on your pivot table options to keep formatting
HTH Rgds
~Geoff~