I have to run my macro twice for it to be correct

Question

I created a macro in Excel which iterates through every worksheet - converting text into numbers and then creating a graph from the data. I need to run my macro twice for it to work.

The 1st time, only the graph for the 1st sheet is correct. For the other tabs, all the text is converted to numbers, but the graphs are not mapping to the right data.

The 2nd time I run the macro, everything is as it should be.

Sub UCS_Inventory_Mac1()

Dim I As Integer
Dim WS_Count As Integer

WS_Count = ActiveWorkbook.Worksheets.Count + 1

For I = 1 To WS_Count

    Range("B2:H15").Select
        With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With

    If I = 1 Then
        Range("B2").Select
        ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
        ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$E$4")
        ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
        ActiveChart.SetElement (msoElementLegendBottom)
        ActiveChart.SetElement (msoElementLegendTop)
        ActiveChart.ChartTitle.Text = "Chart 1"

    ElseIf I = 2 Then
        Worksheets("Sheet2").Activate
        Range("B2").Select
        ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
        ActiveChart.SetSourceData Source:=Range("Sheet2!$A$1:$B$11")
        ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
        ActiveChart.SetElement (msoElementLegendBottom)
        ActiveChart.SetElement (msoElementLegendTop)
        ActiveChart.ChartTitle.Text = "Chart 2"

    ElseIf I = 3 Then
        Worksheets("Sheet3").Activate
        Range("B2").Select
        ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
        ActiveChart.SetSourceData Source:=Range("Sheet3!$A$1:$D$11")
        ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
        ActiveChart.SetElement (msoElementLegendBottom)
        ActiveChart.SetElement (msoElementLegendTop)
        ActiveChart.ChartTitle.Text = "Chart 3"

    ElseIf I = 4 Then
        Worksheets("Sheet4").Activate
        Range("B2").Select
        ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
        ActiveChart.SetSourceData Source:=Range("Sheet4!$A$1:$D$11")
        ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
        ActiveChart.SetElement (msoElementLegendBottom)
        ActiveChart.SetElement (msoElementLegendTop)
        ActiveChart.ChartTitle.Text = "Chart 4"

    ElseIf I = 5 Then
        Worksheets("Sheet5").Activate
        Range("B2").Select
        ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
        ActiveChart.SetSourceData Source:=Range("Sheet5!$A$1:$D$11")
        ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
        ActiveChart.SetElement (msoElementLegendBottom)
        ActiveChart.SetElement (msoElementLegendTop)
        ActiveChart.ChartTitle.Text = "Chart 5"

    ElseIf I = 6 Then
        Worksheets("Sheet6").Activate
        Range("B2").Select
        ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
        ActiveChart.SetSourceData Source:=Range("Sheet6!$A$1:$C$11")
        ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
        ActiveChart.SetElement (msoElementLegendBottom)
        ActiveChart.SetElement (msoElementLegendTop)
        ActiveChart.ChartTitle.Text = "Chart 6"

    ElseIf I = 7 Then
        Worksheets("Sheet7").Activate
        Range("B2").Select
        ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
        ActiveChart.SetSourceData Source:=Range("Sheet7!$A$1:$B$11")
        ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
        ActiveChart.SetElement (msoElementLegendBottom)
        ActiveChart.SetElement (msoElementLegendTop)
        ActiveChart.ChartTitle.Text = "Chart 7"

End If

Next I

End Sub

Answer 1

I took the freedom to clean a bit your code, you might want to try this one (it should work at first time):

Sub UCS_Inventory_Mac1()

Dim I As Integer
Dim WS_Count As Integer

WS_Count = ActiveWorkbook.Worksheets.Count + 1 '<-- please note: the sheets are always 7, why do you set them to 8?

Dim ranges(1 To 7) '<-- this is the only not-indexable part of your code, store it in an array to make the code more readable
ranges(1) = "$A$1:$E$4"
ranges(2) = "$A$1:$B$11"
ranges(3) = "$A$1:$D$11"
ranges(4) = "$A$1:$D$11"
ranges(5) = "$A$1:$D$11"
ranges(6) = "$A$1:$C$11"
ranges(7) = "$A$1:$B$11"

For I = 1 To WS_Count

    With Worksheets("Sheet" & I) '<-- no need to activate the sheet

        With .Range("B2:H15") '<-- even here, no need to select
        .NumberFormat = "General"
        .Value = .Value '<-- this instruction does nothing
        End With

        .Range("B2").Select '<-- do you really need to select the range? This will just make your code flashing through sheets
        With .Shapes.AddChart2(201, xlColumnClustered)
            .SetSourceData Source:=Range("Sheet" & I & "!" & ranges(I))
            .SetElement (msoElementDataLabelOutSideEnd)
            .SetElement (msoElementLegendBottom)
            .SetElement (msoElementLegendTop)
            .ChartTitle.Text = "Chart " & I
        End With

    End With

Next I

End Sub

You will find my comments in the code itself, but I'm pretty sure the problem comes from your misuse of Select / Activate statements that probably lead you to a wrongly qualified range.