Hello everyone,

I am a SAS programmer who has just started to really get into VBA. I produce a report using sas to produce a dataset, then populate an excel template with data. I have made pivot line charts and pivot bar charts, and I have been trying to write VBA code to preserve the format of the charts for each of the series collection. In fact, while probably not efficient, my macros work just fine when I execute them, but as I understand it, the macros are supposed to also preserve formatting. When I run my data in sas, and produce a new report (repopulating the data), the bar charts in my report don't have the correct format. It's not default format, but the bar colors are out of order and the last bar does not have a line border around it. The first macro formats the chart as is, and the second macro runs through the different field options and applies the first macro to each. Below is my code:

Thanks in advace for your help,

Michael


Sub format1()
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart 3").Activate
For I = 1 To 4
ActiveChart.SeriesCollection(I).Select
With Selection.format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.ForeColor.ObjectThemeColor = msoThemeColorText1
.Transparency = 0
End With
Next
With ActiveSheet.ChartObjects("Chart 3")
.Chart.SeriesCollection(1).Interior.Color = RGB(153, 153, 255)
.Chart.SeriesCollection(2).Interior.Color = RGB(153, 51, 102)
.Chart.SeriesCollection(3).Interior.Color = RGB(255, 255, 204)
.Chart.SeriesCollection(4).Interior.Color = RGB(204, 255, 255)
End With
End Sub



Sub format2()
Sheets("BarTables").Visible = True
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("CT").Visible = True
.PivotItems("MRI").Visible = False
.PivotItems("ULS").Visible = False
.PivotItems("MAM").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("MRI").Visible = True
.PivotItems("CT").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("ULS").Visible = True
.PivotItems("MRI").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("MAM").Visible = True
.PivotItems("ULS").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("CT").Visible = True
.PivotItems("MAM").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TACTIC")
.PivotItems("AO Gtr Than Tot ED").Visible = True
.PivotItems("AD Less Than Tot ED").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("MRI").Visible = True
.PivotItems("CT").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("ULS").Visible = True
.PivotItems("MRI").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("MAM").Visible = True
.PivotItems("ULS").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("CT").Visible = True
.PivotItems("MAM").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TACTIC")
.PivotItems("Pnd Less Than Wkly ED").Visible = True
.PivotItems("AO Gtr Than Tot ED").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("MRI").Visible = True
.PivotItems("CT").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("ULS").Visible = True
.PivotItems("MRI").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("MAM").Visible = True
.PivotItems("ULS").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("CT").Visible = True
.PivotItems("MAM").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TACTIC")
.PivotItems("Sply Gtr Than Tot ED").Visible = True
.PivotItems("Pnd Less Than Wkly ED").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("MRI").Visible = True
.PivotItems("CT").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("ULS").Visible = True
.PivotItems("MRI").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("MAM").Visible = True
.PivotItems("ULS").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("CT").Visible = True
.PivotItems("MAM").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TACTIC")
.PivotItems("Open Gtr Than 30% Sply").Visible = True
.PivotItems("Sply Gtr Than Tot ED").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("MRI").Visible = True
.PivotItems("CT").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("ULS").Visible = True
.PivotItems("MRI").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("MAM").Visible = True
.PivotItems("ULS").Visible = False
End With
Application.Run ("format1")
Sheets("BarTables").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MOD")
.PivotItems("CT").Visible = True
.PivotItems("MAM").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TACTIC")
.PivotItems("AD Less Than Tot ED").Visible = True
.PivotItems("Open Gtr Than 30% Sply").Visible = False
End With
Sheets("BarTables").Visible = False
Sheets("Charts").Select
Range("A1").Select
Sheets("Summary").Select
Range("A1").Select
End Sub