How to add a Secondary Axis to a ColumnClustered chart

[aojiku01]

I am trying to figure out, how to add a secondary xlLine axis to the TempChart in the code below.

Sub CreateChart(r As Long, Sts As String)
Dim TempChart As Chart
Dim FName As String
Dim strCol As Long
Dim CatTitles As Range, SrcRange As Range, SourceData As Range
'==============================================================================

'--------------------------------------------------------------------------
'\\\\ GET STATUS START COLUMN
'--------------------------------------------------------------------------
Select Case Sts
Case "Approved"
strCol = "9"
Case "Disputed"
strCol = "17"
Case "Rejected"
strCol = "25"
Case "Un-Reviewed"
strCol = "33"
End Select
'--------------------------------------------------------------------------
With FPws
.Select
fplRow = .Cells(Rows.Count, "A").End(xlUp).Row
fplCol = .Cells(11, Columns.Count).End(xlToLeft).Column

Set CatTitles = .Range(Cells(12, strCol), Cells(12, strCol + 6))
Set SrcRange = .Range(Cells(r, strCol), Cells(r, strCol + 6))
Set SourceData = Union(CatTitles, SrcRange)
End With
'--------------------------------------------------------------------------
'\\\\ ADD A CHART
'--------------------------------------------------------------------------
Application.ScreenUpdating = False
Set TempChart = FPws.Shapes.AddChart.Chart
TempChart.SetSourceData Source:=SourceData

'--------------------------------------------------------------------------
'\\\\ FIX IT UP
'--------------------------------------------------------------------------
With TempChart
.ChartType = xlColumnClustered
.SetSourceData Source:=SourceData, PlotBy:=xlRows
.HasLegend = False
.PlotArea.Interior.ColorIndex = xlNone
.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False
With .Axes(xlValue)
.MajorGridlines.Delete
.MaximumScaleIsAuto = True
'.MaximumScale = FPws.Cells(r, strCol)
End With
.ChartTitle.Text = Format(FPws.Cells(r, "A"), "Mmmm-yyyy") & " Counts"
.ChartArea.Format.Line.Visible = False
.
End With

'--------------------------------------------------------------------------
'\\\\ ADJUST THE CHARTOBJECT'S SIZE
'--------------------------------------------------------------------------
With FPws.ChartObjects(1)
.Width = 235
.Height = 135
End With

'--------------------------------------------------------------------------
'\\\\ SAVE CHART AS GIF
'--------------------------------------------------------------------------
FName = ThisWorkbook.Path & "\temp.gif"
'FName = Application.DefaultFilePath & Application.PathSeparator & "temp.gif"
TempChart.Export fileName:=FName, filterName:="GIF"
FPws.ChartObjects(1).Delete
Application.ScreenUpdating = True
End Sub

 

[btomjack]

I used macro recorder to get this command:
ActiveChart.SeriesCollection(2).AxisGroup = 2

Obviously, that depends on which series you want to put on the secondary axis.