Hi,
I have about 10 graphs, each of which are powered by data held on a specified 'graphdata' page. The data is regularly changing length (i have a max of 12 rows, but sometimes there may only be 6). The graph currently is set default to plot all 12 rows - resulting (sometimes) in there being several blank rows of data on the graph.
So far I have tried something like this:
--Code starts--
Dim GraphSelect, ChartName, GraphName As String
Dim GraphArea As Range
Sheets("ExcelData").Select
GraphArea = Range("A19:F31")
ChartName = "VolRec"
Sheets(GraphSelect).Select
ActiveSheet.ChartObjects(ChartName).Select
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("Exceldata").Range(GraphArea), PlotBy:=xlColumns
--code ends--
The range I specified (a19:f31) is something I want the code to manually work out. It will always start A19 and end F something, but it needs to know how 'deep' to plot the graph.
The error I receive is "object variable or With block variable not set".
In the end I want to be able to 'loop' through the process and have this move onto the next chart and the next data-series.
Any help would be greatly appreciated - i'm the only person in the office today with any of this sort of knowledge and it's rather frustrating.
I have about 10 graphs, each of which are powered by data held on a specified 'graphdata' page. The data is regularly changing length (i have a max of 12 rows, but sometimes there may only be 6). The graph currently is set default to plot all 12 rows - resulting (sometimes) in there being several blank rows of data on the graph.
So far I have tried something like this:
--Code starts--
Dim GraphSelect, ChartName, GraphName As String
Dim GraphArea As Range
Sheets("ExcelData").Select
GraphArea = Range("A19:F31")
ChartName = "VolRec"
Sheets(GraphSelect).Select
ActiveSheet.ChartObjects(ChartName).Select
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("Exceldata").Range(GraphArea), PlotBy:=xlColumns
--code ends--
The range I specified (a19:f31) is something I want the code to manually work out. It will always start A19 and end F something, but it needs to know how 'deep' to plot the graph.
The error I receive is "object variable or With block variable not set".
In the end I want to be able to 'loop' through the process and have this move onto the next chart and the next data-series.
Any help would be greatly appreciated - i'm the only person in the office today with any of this sort of knowledge and it's rather frustrating.