[SOLVED] Why does the chart get created at the end of the worksheets in Debug but not from the button

Why does the chart get created at the end of the worksheets in Debug but not from the button

I'm doing an Excel macro that automatically create a chart as a sheet (not embedded in one) if a specific cell has a value. I want the charts to be put at the end of the worksheets and when I run the code from the MS VB for Applications IDE (if we can call it that) the code works perfectly fine and put my new charts at the end. But when I click on the button to which my macro is linked, the charts are created but not put at the end but instead just before the sheet the button is on.

Of note is that the first 3 sheets are sheets (not charts) and all the next ones are charts as sheet. The one on which my button is on is the last sheet just before the charts. Is there a possibility that the macro doesn't see the charts as sheet. Even though it'd be strange as it still would put it one before last place and not at the end.

Here's my code:

Sub CreateCharts()

    Call BaseFunctions

    If Not YesNoMessageBox() Then
       Exit Sub
    End If

    Const FIRST_ROW As Integer = 3
    Const LAST_ROW As Integer = 32
    
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Year")
    
    Dim i As Integer
    For i = FIRST_ROW To LAST_ROW
        
        Dim nameCell As String
        nameCell = Trim(ws.Cells(i, 3))
        If nameCell = "" Then
            Exit For
        End If
        
        DeleteWorksheet (nameCell)
        
        Dim newChart As Chart
        Dim count As Integer
        count = Sheets.count
        Set newChart = wb.Charts.Add2(After:=Sheets(Sheets.count), NewLayout:=True)
        newChart.ChartType = xlLineMarkers
        newChart.SetSourceData Source:=Range(ws.Cells(i, 4), ws.Cells(i, 19))
        newChart.ChartTitle.Text = nameCell
        newChart.Name = nameCell
        newChart.Axes(xlValue).MinimumScale = 1
        newChart.Axes(xlValue).MaximumScale = 6
        newChart.Axes(xlValue).MajorUnit = 1
        newChart.Axes(xlValue).MinorUnit = 0.1
        newChart.SetElement (msoElementPrimaryValueGridLinesMinorMajor)
        newChart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
        
    Next i
End Sub

I also tried to create the sheet without specifying the location then moving it at the end of the creation but at best I get a message that tells me it's out of range, and at worst Excel crashes.

Replacing

Set newChart = wb.Charts.Add2(After:=Sheets(Sheets.count), NewLayout:=True)

'Set newChart = wb.Charts.Add2(NewLayout:=True)

and using

Sheets(nameCell).Move After:=Sheets(count) before Next i

Solution

So following what you all wrote, I changed my way of doing thing. I now create the charts in the current sheet then move them later. I will also try to create them as sheet but for not it works.

Here's the relevant parts.

Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Worksheets("Year")

ws.Select
ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select

[...]

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=nameCell
Sheets(nameCell).Select
Sheets(nameCell).Move After:=Sheets(Sheets.count)