Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to format all pivotcharts in a workbook
You know how Pivotcharts lose their formatting when you refresh the pivot
table they are attached to? Well I set up a macro to re-format the charts, but my code only formats the currently selected chart. How can I change my code to update all the pivot charts in my workbook? Here's the code I have right now: ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False ActiveChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to format all pivotcharts in a workbook
Jeremy,
Suggestion: After creating your Pivot Table, go back into 'Table Options' and uncheck 'AutoFormat table'. This will stop Excel from automatically reformatting the Pivot Table that you just took so much time to format exactly as you want it. Here's a macro to change the AutoFormat option in all your Pivot Tables in the active workbook. '/-------------------------------------/ Sub ChangeAutoFormatting() Dim iSheets As Integer, x As Integer Dim iPivot As Integer, _ strCurrentSheet As String On Error GoTo Exit_ChgAutoFmt 'Count number of sheets in workbook iSheets = ActiveWorkbook.Sheets.Count 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_ChgAutoFmt For x = 1 To iSheets 'go to a worksheet to change pivot tables Sheets(x).Activate 'turn warning messages off Application.DisplayAlerts = False 'change all pivot tables on ' this worksheet one at a time For iPivot = 1 To _ ActiveSheet.PivotTables.Count ActiveSheet.PivotTables(iPivot). _ HasAutoFormat = False Next 'turn warning messages on Application.DisplayAlerts = True Next 'return to worksheet that ' you were originally at Application.ActiveWorkbook. _ Sheets(strCurrentSheet).Activate Exit_ChgAutoFmt: Application.CommandBars("PivotTable"). _ Visible = False Application.DisplayAlerts = True End Sub '/-------------------------------------/ HTH, Gary Brown "jeremy nickels" wrote: You know how Pivotcharts lose their formatting when you refresh the pivot table they are attached to? Well I set up a macro to re-format the charts, but my code only formats the currently selected chart. How can I change my code to update all the pivot charts in my workbook? Here's the code I have right now: ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False ActiveChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to format all pivotcharts in a workbook
Gary,
It's not the pivot tables that I have a problem with, it's the pivot charts that are linked to those pivot tables. Even when I remove "AutoFormat table" on the pivot tables, the pivot charts linked to those pivot tables lose their formatting when I refresh the data. I read something in Excel help that said that it is not possible to retain formatting on a pivot chart when the pivot table that it is linked to is refreshed. It suggested writing a macro to re-format the charts after you refresh. I wrote the macro, but it only re-formats one chart at a time. I want it to re-format all the charts in a workbook, if possible. "Gary Brown" wrote: Jeremy, Suggestion: After creating your Pivot Table, go back into 'Table Options' and uncheck 'AutoFormat table'. This will stop Excel from automatically reformatting the Pivot Table that you just took so much time to format exactly as you want it. Here's a macro to change the AutoFormat option in all your Pivot Tables in the active workbook. '/-------------------------------------/ Sub ChangeAutoFormatting() Dim iSheets As Integer, x As Integer Dim iPivot As Integer, _ strCurrentSheet As String On Error GoTo Exit_ChgAutoFmt 'Count number of sheets in workbook iSheets = ActiveWorkbook.Sheets.Count 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_ChgAutoFmt For x = 1 To iSheets 'go to a worksheet to change pivot tables Sheets(x).Activate 'turn warning messages off Application.DisplayAlerts = False 'change all pivot tables on ' this worksheet one at a time For iPivot = 1 To _ ActiveSheet.PivotTables.Count ActiveSheet.PivotTables(iPivot). _ HasAutoFormat = False Next 'turn warning messages on Application.DisplayAlerts = True Next 'return to worksheet that ' you were originally at Application.ActiveWorkbook. _ Sheets(strCurrentSheet).Activate Exit_ChgAutoFmt: Application.CommandBars("PivotTable"). _ Visible = False Application.DisplayAlerts = True End Sub '/-------------------------------------/ HTH, Gary Brown "jeremy nickels" wrote: You know how Pivotcharts lose their formatting when you refresh the pivot table they are attached to? Well I set up a macro to re-format the charts, but my code only formats the currently selected chart. How can I change my code to update all the pivot charts in my workbook? Here's the code I have right now: ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False ActiveChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to format all pivotcharts in a workbook
Sorry I misunderstood. Try this...
'/-------------------------------------/ Sub ChangePivotFormatting() Dim cChart As Chart Dim strCurrentSheet As String On Error GoTo err_ChgPivotFmt 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_ChgPivotFmt For Each cChart In Charts cChart.Activate cChart.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False cChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With Next cChart 'return to worksheet that ' you were originally at Application.ActiveWorkbook. _ Sheets(strCurrentSheet).Activate Exit_ChgPivotFmt: Exit Sub err_ChgPivotFmt: Debug.Print ActiveSheet.Name & " - " & _ Err.Number & " - " & Err.Description GoTo Exit_ChgPivotFmt End Sub '/-------------------------------------/ HTH, Gary Brown "jeremy nickels" wrote: Gary, It's not the pivot tables that I have a problem with, it's the pivot charts that are linked to those pivot tables. Even when I remove "AutoFormat table" on the pivot tables, the pivot charts linked to those pivot tables lose their formatting when I refresh the data. I read something in Excel help that said that it is not possible to retain formatting on a pivot chart when the pivot table that it is linked to is refreshed. It suggested writing a macro to re-format the charts after you refresh. I wrote the macro, but it only re-formats one chart at a time. I want it to re-format all the charts in a workbook, if possible. "Gary Brown" wrote: Jeremy, Suggestion: After creating your Pivot Table, go back into 'Table Options' and uncheck 'AutoFormat table'. This will stop Excel from automatically reformatting the Pivot Table that you just took so much time to format exactly as you want it. Here's a macro to change the AutoFormat option in all your Pivot Tables in the active workbook. '/-------------------------------------/ Sub ChangeAutoFormatting() Dim iSheets As Integer, x As Integer Dim iPivot As Integer, _ strCurrentSheet As String On Error GoTo Exit_ChgAutoFmt 'Count number of sheets in workbook iSheets = ActiveWorkbook.Sheets.Count 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_ChgAutoFmt For x = 1 To iSheets 'go to a worksheet to change pivot tables Sheets(x).Activate 'turn warning messages off Application.DisplayAlerts = False 'change all pivot tables on ' this worksheet one at a time For iPivot = 1 To _ ActiveSheet.PivotTables.Count ActiveSheet.PivotTables(iPivot). _ HasAutoFormat = False Next 'turn warning messages on Application.DisplayAlerts = True Next 'return to worksheet that ' you were originally at Application.ActiveWorkbook. _ Sheets(strCurrentSheet).Activate Exit_ChgAutoFmt: Application.CommandBars("PivotTable"). _ Visible = False Application.DisplayAlerts = True End Sub '/-------------------------------------/ HTH, Gary Brown "jeremy nickels" wrote: You know how Pivotcharts lose their formatting when you refresh the pivot table they are attached to? Well I set up a macro to re-format the charts, but my code only formats the currently selected chart. How can I change my code to update all the pivot charts in my workbook? Here's the code I have right now: ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False ActiveChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to format all pivotcharts in a workbook
Gary, it doesn't seem to be working. Let me describe my workbook in a little
more detail. I have about 18 worksheets in this workbook. Each worksheet has 3 charts on it. Does knowing that change the code at all? "Gary Brown" wrote: Sorry I misunderstood. Try this... '/-------------------------------------/ Sub ChangePivotFormatting() Dim cChart As Chart Dim strCurrentSheet As String On Error GoTo err_ChgPivotFmt 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_ChgPivotFmt For Each cChart In Charts cChart.Activate cChart.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False cChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With Next cChart 'return to worksheet that ' you were originally at Application.ActiveWorkbook. _ Sheets(strCurrentSheet).Activate Exit_ChgPivotFmt: Exit Sub err_ChgPivotFmt: Debug.Print ActiveSheet.Name & " - " & _ Err.Number & " - " & Err.Description GoTo Exit_ChgPivotFmt End Sub '/-------------------------------------/ HTH, Gary Brown "jeremy nickels" wrote: Gary, It's not the pivot tables that I have a problem with, it's the pivot charts that are linked to those pivot tables. Even when I remove "AutoFormat table" on the pivot tables, the pivot charts linked to those pivot tables lose their formatting when I refresh the data. I read something in Excel help that said that it is not possible to retain formatting on a pivot chart when the pivot table that it is linked to is refreshed. It suggested writing a macro to re-format the charts after you refresh. I wrote the macro, but it only re-formats one chart at a time. I want it to re-format all the charts in a workbook, if possible. "Gary Brown" wrote: Jeremy, Suggestion: After creating your Pivot Table, go back into 'Table Options' and uncheck 'AutoFormat table'. This will stop Excel from automatically reformatting the Pivot Table that you just took so much time to format exactly as you want it. Here's a macro to change the AutoFormat option in all your Pivot Tables in the active workbook. '/-------------------------------------/ Sub ChangeAutoFormatting() Dim iSheets As Integer, x As Integer Dim iPivot As Integer, _ strCurrentSheet As String On Error GoTo Exit_ChgAutoFmt 'Count number of sheets in workbook iSheets = ActiveWorkbook.Sheets.Count 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_ChgAutoFmt For x = 1 To iSheets 'go to a worksheet to change pivot tables Sheets(x).Activate 'turn warning messages off Application.DisplayAlerts = False 'change all pivot tables on ' this worksheet one at a time For iPivot = 1 To _ ActiveSheet.PivotTables.Count ActiveSheet.PivotTables(iPivot). _ HasAutoFormat = False Next 'turn warning messages on Application.DisplayAlerts = True Next 'return to worksheet that ' you were originally at Application.ActiveWorkbook. _ Sheets(strCurrentSheet).Activate Exit_ChgAutoFmt: Application.CommandBars("PivotTable"). _ Visible = False Application.DisplayAlerts = True End Sub '/-------------------------------------/ HTH, Gary Brown "jeremy nickels" wrote: You know how Pivotcharts lose their formatting when you refresh the pivot table they are attached to? Well I set up a macro to re-format the charts, but my code only formats the currently selected chart. How can I change my code to update all the pivot charts in my workbook? Here's the code I have right now: ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False ActiveChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to format all pivotcharts in a workbook
Yes.
I assumed (made an ASS out of U and ME) that you were talking about chart worksheets. I've included code for multiple chart objects on various worksheets within a workbook. Try this (knock on wood - NO! Not my head :O!) '/-------------------------------------/ Sub ChangePivotFormatting() Dim cChart As Chart Dim coChart As ChartObject Dim sht As Worksheet Dim strCurrentSheet As String On Error GoTo err_ChgPivotFmt 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_ChgPivotFmt For Each cChart In Charts cChart.Activate cChart.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False cChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With Next cChart For Each sht In Worksheets For Each coChart In sht.ChartObjects coChart.Activate ActiveChart.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False ActiveChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With Next coChart Next sht 'return to worksheet that ' you were originally at Application.ActiveWorkbook. _ Sheets(strCurrentSheet).Activate Exit_ChgPivotFmt: Exit Sub err_ChgPivotFmt: Debug.Print ActiveSheet.Name & " - " & _ Err.Number & " - " & Err.Description GoTo Exit_ChgPivotFmt End Sub '/-------------------------------------/ "jeremy nickels" wrote: Gary, it doesn't seem to be working. Let me describe my workbook in a little more detail. I have about 18 worksheets in this workbook. Each worksheet has 3 charts on it. Does knowing that change the code at all? "Gary Brown" wrote: Sorry I misunderstood. Try this... '/-------------------------------------/ Sub ChangePivotFormatting() Dim cChart As Chart Dim strCurrentSheet As String On Error GoTo err_ChgPivotFmt 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_ChgPivotFmt For Each cChart In Charts cChart.Activate cChart.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False cChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With Next cChart 'return to worksheet that ' you were originally at Application.ActiveWorkbook. _ Sheets(strCurrentSheet).Activate Exit_ChgPivotFmt: Exit Sub err_ChgPivotFmt: Debug.Print ActiveSheet.Name & " - " & _ Err.Number & " - " & Err.Description GoTo Exit_ChgPivotFmt End Sub '/-------------------------------------/ HTH, Gary Brown "jeremy nickels" wrote: Gary, It's not the pivot tables that I have a problem with, it's the pivot charts that are linked to those pivot tables. Even when I remove "AutoFormat table" on the pivot tables, the pivot charts linked to those pivot tables lose their formatting when I refresh the data. I read something in Excel help that said that it is not possible to retain formatting on a pivot chart when the pivot table that it is linked to is refreshed. It suggested writing a macro to re-format the charts after you refresh. I wrote the macro, but it only re-formats one chart at a time. I want it to re-format all the charts in a workbook, if possible. "Gary Brown" wrote: Jeremy, Suggestion: After creating your Pivot Table, go back into 'Table Options' and uncheck 'AutoFormat table'. This will stop Excel from automatically reformatting the Pivot Table that you just took so much time to format exactly as you want it. Here's a macro to change the AutoFormat option in all your Pivot Tables in the active workbook. '/-------------------------------------/ Sub ChangeAutoFormatting() Dim iSheets As Integer, x As Integer Dim iPivot As Integer, _ strCurrentSheet As String On Error GoTo Exit_ChgAutoFmt 'Count number of sheets in workbook iSheets = ActiveWorkbook.Sheets.Count 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_ChgAutoFmt For x = 1 To iSheets 'go to a worksheet to change pivot tables Sheets(x).Activate 'turn warning messages off Application.DisplayAlerts = False 'change all pivot tables on ' this worksheet one at a time For iPivot = 1 To _ ActiveSheet.PivotTables.Count ActiveSheet.PivotTables(iPivot). _ HasAutoFormat = False Next 'turn warning messages on Application.DisplayAlerts = True Next 'return to worksheet that ' you were originally at Application.ActiveWorkbook. _ Sheets(strCurrentSheet).Activate Exit_ChgAutoFmt: Application.CommandBars("PivotTable"). _ Visible = False Application.DisplayAlerts = True End Sub '/-------------------------------------/ HTH, Gary Brown "jeremy nickels" wrote: You know how Pivotcharts lose their formatting when you refresh the pivot table they are attached to? Well I set up a macro to re-format the charts, but my code only formats the currently selected chart. How can I change my code to update all the pivot charts in my workbook? Here's the code I have right now: ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False ActiveChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to format all pivotcharts in a workbook
That did the trick Gary. You are a genius!
"Gary Brown" wrote: Yes. I assumed (made an ASS out of U and ME) that you were talking about chart worksheets. I've included code for multiple chart objects on various worksheets within a workbook. Try this (knock on wood - NO! Not my head :O!) '/-------------------------------------/ Sub ChangePivotFormatting() Dim cChart As Chart Dim coChart As ChartObject Dim sht As Worksheet Dim strCurrentSheet As String On Error GoTo err_ChgPivotFmt 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_ChgPivotFmt For Each cChart In Charts cChart.Activate cChart.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False cChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With Next cChart For Each sht In Worksheets For Each coChart In sht.ChartObjects coChart.Activate ActiveChart.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False ActiveChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With Next coChart Next sht 'return to worksheet that ' you were originally at Application.ActiveWorkbook. _ Sheets(strCurrentSheet).Activate Exit_ChgPivotFmt: Exit Sub err_ChgPivotFmt: Debug.Print ActiveSheet.Name & " - " & _ Err.Number & " - " & Err.Description GoTo Exit_ChgPivotFmt End Sub '/-------------------------------------/ "jeremy nickels" wrote: Gary, it doesn't seem to be working. Let me describe my workbook in a little more detail. I have about 18 worksheets in this workbook. Each worksheet has 3 charts on it. Does knowing that change the code at all? "Gary Brown" wrote: Sorry I misunderstood. Try this... '/-------------------------------------/ Sub ChangePivotFormatting() Dim cChart As Chart Dim strCurrentSheet As String On Error GoTo err_ChgPivotFmt 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_ChgPivotFmt For Each cChart In Charts cChart.Activate cChart.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False cChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With Next cChart 'return to worksheet that ' you were originally at Application.ActiveWorkbook. _ Sheets(strCurrentSheet).Activate Exit_ChgPivotFmt: Exit Sub err_ChgPivotFmt: Debug.Print ActiveSheet.Name & " - " & _ Err.Number & " - " & Err.Description GoTo Exit_ChgPivotFmt End Sub '/-------------------------------------/ HTH, Gary Brown "jeremy nickels" wrote: Gary, It's not the pivot tables that I have a problem with, it's the pivot charts that are linked to those pivot tables. Even when I remove "AutoFormat table" on the pivot tables, the pivot charts linked to those pivot tables lose their formatting when I refresh the data. I read something in Excel help that said that it is not possible to retain formatting on a pivot chart when the pivot table that it is linked to is refreshed. It suggested writing a macro to re-format the charts after you refresh. I wrote the macro, but it only re-formats one chart at a time. I want it to re-format all the charts in a workbook, if possible. "Gary Brown" wrote: Jeremy, Suggestion: After creating your Pivot Table, go back into 'Table Options' and uncheck 'AutoFormat table'. This will stop Excel from automatically reformatting the Pivot Table that you just took so much time to format exactly as you want it. Here's a macro to change the AutoFormat option in all your Pivot Tables in the active workbook. '/-------------------------------------/ Sub ChangeAutoFormatting() Dim iSheets As Integer, x As Integer Dim iPivot As Integer, _ strCurrentSheet As String On Error GoTo Exit_ChgAutoFmt 'Count number of sheets in workbook iSheets = ActiveWorkbook.Sheets.Count 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_ChgAutoFmt For x = 1 To iSheets 'go to a worksheet to change pivot tables Sheets(x).Activate 'turn warning messages off Application.DisplayAlerts = False 'change all pivot tables on ' this worksheet one at a time For iPivot = 1 To _ ActiveSheet.PivotTables.Count ActiveSheet.PivotTables(iPivot). _ HasAutoFormat = False Next 'turn warning messages on Application.DisplayAlerts = True Next 'return to worksheet that ' you were originally at Application.ActiveWorkbook. _ Sheets(strCurrentSheet).Activate Exit_ChgAutoFmt: Application.CommandBars("PivotTable"). _ Visible = False Application.DisplayAlerts = True End Sub '/-------------------------------------/ HTH, Gary Brown "jeremy nickels" wrote: You know how Pivotcharts lose their formatting when you refresh the pivot table they are attached to? Well I set up a macro to re-format the charts, but my code only formats the currently selected chart. How can I change my code to update all the pivot charts in my workbook? Here's the code I have right now: ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _ AutoText:=True, LegendKey:=False ActiveChart.SeriesCollection(1).DataLabels.Select Selection.AutoScaleFont = False With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 .Background = xlTransparent End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotCharts and recalculating workbooks | Excel Discussion (Misc queries) | |||
Linking Multiple pivotcharts | Charts and Charting in Excel | |||
Dates in Excel PivotCharts | Charts and Charting in Excel | |||
Having an issue with some pivotcharts? | Charts and Charting in Excel | |||
PivotCharts (max cell/row limits)? | Charts and Charting in Excel |