Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to update Chart Source Data Rage on Multiple Charts
What I'm trying to do is to have a macro automatically change the number of
series and categories on a series of charts. Each chart refers to a different data sheet. I recorded a macro to delete the data range in each chart, then copy a dynamic range from the data sheet and pasted it into the chart. This code currently dies on the ActiveChart.Delete line. When I recorded the macro, it created that line when I went into chartSourceData and deleted the DataRange. When I did that, it seemed to keep the chart structure in place, just removed the data. I'll paste the exact recorded macro below my code for reference. Thanks for any help! MikeZz Basic background info: Charts are all on sheet "Report". Charts are named "ChartA", "ChartB", etc... Data for ChartA is on sheet "A", Data for ChartB is on sheet "B" Each data sheet (A,B, etc) have a range called "ChartData" Sub My_Modified_Code() Dim oChart As ChartObject Dim cht As Object, sh As Worksheet For Each oChart In ActiveSheet.ChartObjects chtName = oChart.Name chtSheet = Replace(chtName, "Chart", "") ActiveSheet.ChartObjects(chtName).Activate ActiveChart.ChartArea.Select Application.CutCopyMode = False ActiveChart.Delete ActiveWindow.Visible = False Windows("Measurables Chart 8-Panel v7.xls").Activate Sheets(chtSheet).Select Application.Goto Reference:="ChartData" Selection.Copy Sheets("Report").Select ActiveSheet.ChartObjects(chtName).Activate ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=True, _ CategoryLabels:=True, Replace:=True, NewSeries:=True Next End Sub sub Recorded_Macro ActiveSheet.ChartObjects("ChartA").Activate ActiveChart.ChartArea.Select Application.CutCopyMode = False ActiveChart.Delete ActiveWindow.Visible = False Windows("Measurables Chart 8-Panel v7.xls").Activate Sheets("B").Select Application.Goto Reference:="ChartData" Selection.Copy Sheets("Report").Select ActiveSheet.ChartObjects("ChartA").Activate ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=True, _ CategoryLabels:=True, Replace:=True, NewSeries:=True end sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to update Chart Source Data Rage on Multiple Charts
Mike -
See my response in the other newsgroup. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "MikeZz" wrote in message ... What I'm trying to do is to have a macro automatically change the number of series and categories on a series of charts. Each chart refers to a different data sheet. I recorded a macro to delete the data range in each chart, then copy a dynamic range from the data sheet and pasted it into the chart. This code currently dies on the ActiveChart.Delete line. When I recorded the macro, it created that line when I went into chartSourceData and deleted the DataRange. When I did that, it seemed to keep the chart structure in place, just removed the data. I'll paste the exact recorded macro below my code for reference. Thanks for any help! MikeZz Basic background info: Charts are all on sheet "Report". Charts are named "ChartA", "ChartB", etc... Data for ChartA is on sheet "A", Data for ChartB is on sheet "B" Each data sheet (A,B, etc) have a range called "ChartData" Sub My_Modified_Code() Dim oChart As ChartObject Dim cht As Object, sh As Worksheet For Each oChart In ActiveSheet.ChartObjects chtName = oChart.Name chtSheet = Replace(chtName, "Chart", "") ActiveSheet.ChartObjects(chtName).Activate ActiveChart.ChartArea.Select Application.CutCopyMode = False ActiveChart.Delete ActiveWindow.Visible = False Windows("Measurables Chart 8-Panel v7.xls").Activate Sheets(chtSheet).Select Application.Goto Reference:="ChartData" Selection.Copy Sheets("Report").Select ActiveSheet.ChartObjects(chtName).Activate ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=True, _ CategoryLabels:=True, Replace:=True, NewSeries:=True Next End Sub sub Recorded_Macro ActiveSheet.ChartObjects("ChartA").Activate ActiveChart.ChartArea.Select Application.CutCopyMode = False ActiveChart.Delete ActiveWindow.Visible = False Windows("Measurables Chart 8-Panel v7.xls").Activate Sheets("B").Select Application.Goto Reference:="ChartData" Selection.Copy Sheets("Report").Select ActiveSheet.ChartObjects("ChartA").Activate ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=True, _ CategoryLabels:=True, Replace:=True, NewSeries:=True end sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple charts from 1 source data | Charts and Charting in Excel | |||
change source data in multiple charts | Charts and Charting in Excel | |||
Update a chart immediately after inputting data into data source | Charts and Charting in Excel | |||
Macro to Update Charts Source Data | Charts and Charting in Excel | |||
Chart/Source Data update problem | Charts and Charting in Excel |