VBA Generated Spider/Radar Plot Not Displaying After Macro Run

[toiner]

I have a task to update an Excel tool we use at my job to update/improve functionality. Unfortunately I am building on some already existing VBA code which has made things slightly trickier. The functionality I was trying to add was a simple macro (button press) to create a spider/radar plot of the data along with some extra layers which represent points of progression through a project. I've managed to get all of the functionality working that I wanted but the issue I'm having is that the macro runs and creates the chart, all the data is selected correctly, but the image doesn't show until I save the spreadsheet, close and open again. I've attached my code and a couple of screenshots to try to explain it clearer.


Apologies in advance for the state of my coding, as I said I'm not that experienced and have pieced it together from various learnings on the internet.

Sub Spider()



'''''''''''''''''''''''''''''''''''''''''''''''

'Delete Spider Chart Sheets That Already Exist'

'''''''''''''''''''''''''''''''''''''''''''''''



Dim mySheetName As String

mySheetName = "Spider Chart"

Application.DisplayAlerts = False

On Error Resume Next

ThisWorkbook.Sheets(mySheetName).Delete

On Error GoTo 0

Application.DisplayAlerts = True



'''''''''''''''''''''''''''

'Check How Many Rows Exist'

'''''''''''''''''''''''''''



Dim LastRow As Integer

   LastRow = 10                   

   While Sheets("TRLs").Cells(LastRow, 1) <> ""   

       LastRow = LastRow + 1

   Wend

   LastRow = LastRow - 1



''''''''''''''''''''''''''''''''''''''

'Set Up Constant Value Arrays To Plot'

''''''''''''''''''''''''''''''''''''''



Dim arr_length As Integer

arr_length = LastRow - 10

Dim Concept() As Integer

ReDim Concept(arr_length)

Dim Ass_Phase() As Integer

ReDim Ass_Phase(arr_length)

Dim Dev_Phase() As Integer

ReDim Dev_Phase(arr_length)

Dim Risk_Red() As Integer

ReDim Risk_Red(arr_length)

Dim Qualif() As Integer

ReDim Qualif(arr_length)

Dim Production() As Integer

ReDim Production(arr_length)

Dim Filled() As Integer

ReDim Filled(arr_length)

    Dim intI As Integer

    For intI = 0 To arr_length

    Concept(intI) = 1

    Ass_Phase(intI) = 3

    Dev_Phase(intI) = 4

    Risk_Red(intI) = 5

    Qualif(intI) = 6

    Production(intI) = 7

    Filled(intI) = 9

    Next intI

    

'''''''''''''''''''''''''''''''''''''

'Set Up New Chart Sheet and Populate'

'''''''''''''''''''''''''''''''''''''

    

Dim ChartSheet_Spider As Chart







Set ChartSheet_Spider = Charts.Add

With ChartSheet_Spider

    .ChartType = xlRadarFilled

    .HasTitle = True



    .SeriesCollection.NewSeries

    .SeriesCollection.NewSeries

    .SeriesCollection.NewSeries

    .SeriesCollection.NewSeries

    .SeriesCollection.NewSeries

    .SeriesCollection.NewSeries

    .SeriesCollection.NewSeries

            

    With .SeriesCollection(1)

        .XValues = "='TRLs'!B10:B" & LastRow ' New Titles Range"

        .Values = Filled

        .Name = "Full TRL"

    End With

    

    With .SeriesCollection(2)

        .XValues = "='TRLs'!B10:B" & LastRow ' New Titles Range"

        .Values = Production

        .Name = "Production Phase"

    End With

        

    With .SeriesCollection(3)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = Qualif

        .Name = "Qualification Phase"

    End With

    

    With .SeriesCollection(4)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = Risk_Red

        .Name = "Risk Reduction Phase"

    End With

    

    .SeriesCollection.NewSeries

    With .SeriesCollection(5)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = Dev_Phase

        .Name = "Development Phase"

    End With

    

    With .SeriesCollection(6)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = Dev_Phase

        .Name = "Development Phase"

    End With

    

    With .SeriesCollection(7)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = Ass_Phase

        .Name = "Assessment Phase"

    End With

    

    With .SeriesCollection(8)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = Concept

        .Name = "Concept Design Phase"

    End With

    

    With .SeriesCollection(9)

        .XValues = "='TRLs'!B10:B" & LastRow ' "

        .Values = "='TRLs'!C10:C" & LastRow ' "

        .Name = "Current Levels"

        .Format.Fill.Solid

        .Format.Fill.Transparency = 0.2

    End With

    

    .ChartTitle.Text = Sheets("DMA Syst Eval").Range("A4") & " TRL"

    .Name = "Spider Chart"

    

End With



Worksheets("TRLs").Activate



End Sub