VBA help for auto creating Graphs. Current Code included

[Mitchell Clarke]

Hi there,

I am new to VBA and have used the macro recorder to record the process I am trying to achieve. The issue I am having is that when the length of the column changes my Macro is stuck to the same column length (range) I used for the original graph on the Macro Recorder.

Effectively I have 31 columns of data which can have differing lengths of data. Column one is always the date column. I am them trying to create a graph for Column 1 with Column 2, then Column 1 with Column 3 and then column 1 with column 4 etc.

The code the Macro recorder has generated is below:

Sheets("Phase Correction and kWh per HH").Select

Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A3:B3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range( _
"'Phase Correction and kWh per HH'!$A$3:$B$817")
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
ActiveChart.ChartArea.Select
ActiveChart.Parent.Cut
Sheets("Graphs").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Phase Correction and kWh per HH").Select
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A3:A817,C3").Select
Range("C3").Activate
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range( _
"'Phase Correction and kWh per HH'!$A$3:$A$817,'Phase Correction and kWh per HH'!$C$3:$C$817" _
)
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
ActiveChart.ChartArea.Select
ActiveChart.Parent.Cut
Sheets("Graphs").Select
Range("A18").Select
ActiveSheet.Paste
Range("L23").Select


Any help would be much appreciated.