Hi ramankumar,
I would suggest you manually create the chart so that you can set the size and formatting exactly as you want it, but then let your VBA code add or subtract series depending on the need at the time. Here is a bit of code that might be instructed. I apologize in advance that there may be a bunch of extra stuff here to disctract you, but I don't want to leave anything out that might be helpful either. This code adds or subtracts series from a chart depending on how many columns of data exist on the PD worksheet. These columns are inserted or deleted by code earlier in the macro (not shown). nColsNew is the number of columns of data now, and nColsOld is the number of columns (series) of data the chart is currently plotting.
Application.StatusBar = "Modifying SA chart"
Dim LastPDrow As Integer
Dim TimeCol As Integer
Dim TimeRange As Range
Dim TempRange As Range
Dim NewSeries As Series
TimeCol = PD.Range("TimeColumn").Column
LastPDrow = PD.Range("TimeColumn").Offset(2).End(xlDown).Row
Set TimeRange = PD.Range(PD.Cells(4, TimeCol), PD.Cells(LastPDrow, TimeCol))
Col = PD.Range(ColName(1)).Column
Set TempRange = PD.Range(PD.Cells(4, Col), PD.Cells(LastPDrow, Col))
With Charts("SA Plot")
If nColsNew > nColsOld Then 'Increase in columns
For i = nColsOld + 1 To nColsNew
Set NewSeries = .SeriesCollection.NewSeries
NewSeries.MarkerStyle = xlMarkerStyleNone
NewSeries.Name = "Panel Type" & Str(i) & " Temp."
NewSeries.AxisGroup = 2
NewSeries.Values = TempRange.Offset(0, i - 1)
Next i
Else
' Assume Temp. series are last series added to chart
j = .SeriesCollection.Count
For i = j To j - nColsOld + nColsNew + 1 Step -1
.SeriesCollection(i).Delete
Next i
End If
End With
If your chart is embedded on a worksheet the
With Charts("SA Plot")
would become something like
With ChartObjects("SA Plot").Chart
assuming that you had given it the name "SA Plot" (in the formula bar Name box above cell A1).
I hope this helps.
