VBA Pie Chart craziness!

[LNG2013]

PLEASE PLEASE HELP

I for the life of me cannot figure this out, I have spent the last few days trying to figure out what is wrong....

With the code below it creates a pie chart from the data in G2:J2, and then the row in G through J (Should be something like (G37:J37).

Unfortunately the code keeps selecting the last row of G through J through G2:J2.... So instead of selecting 8 cells of data it selects like 140+. I think the issue is with one of the bolded parts below...


A link to the chart can be found here: http://db.tt/Uqxunp8B

Thank you in advance to anyone who can supply me with any input.

Sub Add_PVVrGChart()
Dim co As ChartObject, endg%, i%, sname$, suffix, [B]s$, r$[/B]
'~~~ Suffix allows the code to be manipulated more easily with changing the sheet name but keeping the Suffix the same
suffix = Array("A", "B", "C", "D", "E", "F", "G", "H")
For i = LBound(suffix) To UBound(suffix)
    On Error Resume Next
    
    '~~~ name of sheet is currently set for Objective
    sname = "Data" & suffix(i)
    
    
[B]      endg = Sheets(sname).Range("g65536").End(xlUp).Row[/B]
[B]    r = "G" & endg & ":J" & endg
    s = "G2:J2"
[/B]    
    
    '~~~ This area of the code indicates the shape and location of the chart
    Set co = Worksheets(sname).ChartObjects.Add(Left:=Cells(1, 9).Left, Width:=225, _
    Top:=Cells(endg + 7, 1).Top, Height:=200)
    On Error Resume Next
    
    '~~~ This section of the code indicates the properties of the chart
    With co.Chart
        .SetSourceData Source:=Sheets(sname).Range(s, r), PlotBy:=xlRows
        '.SetSourceData Source:=Sheets(sname).Union(Range("G2:J2"), Range(r)), PlotBy:=xlRows
        .ChartType = xlPie
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
            With .ChartArea.Border
                .ColorIndex = 57
                .ColorIndex = 57
                .Weight = 2
                .LineStyle = 1
            End With
            With .Legend.LegendEntries(1).LegendKey.Interior
                .ColorIndex = 3
                .Pattern = xlSolid
            End With
            With .Legend.LegendEntries(2).LegendKey.Interior
                .ColorIndex = 44
                .Pattern = xlSolid
            End With
              
            With .Legend.LegendEntries(3).LegendKey.Interior
                .ColorIndex = 41
                .Pattern = xlSolid
            End With
            
            With .Legend.LegendEntries(4).LegendKey.Interior
                .ColorIndex = 4
                .Pattern = xlSolid
            End With
            With .Legend
            .Position = xlLegendPositionBottom
            .Fill.TwoColorGradient Style:=msoGradientVertical, Variant:=1
            .Fill.Visible = True
            .Fill.ForeColor.SchemeColor = 37
            .Fill.BackColor.SchemeColor = 2
            End With
            With .PlotArea.Border
                .Weight = xlThin
                .LineStyle = xlNone
            End With
            With .PlotArea.Interior
                .ColorIndex = 2
                .PatternColorIndex = 1
                .Pattern = xlSolid
            End With
              
         With .Legend.Font
        .Name = "Arial Rounded MT Bold"
        .FontStyle = "Regular"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
        End With
            
    End With
      Sheets(sname).ChartObjects.RoundedCorners = True
      Sheets(sname).ChartObjects.Shadow = True
      
    ActiveChart.PlotArea.Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With
Next
   On Error GoTo 0
   End Sub

 

[LNG2013]

I am giving this one bump because I really really could use assistance with this, I have tried everything I can think of.... Please help