2007 Pie Chart fails in 2003 (Code included)

[LNG2013]

Heya Everyone,

The VBA code I am using for creating a pie chart works in 2007 but fails in 2003. Essentially it looks for all sheets with Test in A1 and then when located, selects G2:J2, then the last row of G:J to make the pie chart. It fails in the bolded section section. (Thanks to Mike for the original help with the code)

Sub CreatePieChart()
Dim X, Y As Long
Dim DataArray(2, 4) As Variant
Dim WSheet As Worksheet
 
Application.ScreenUpdating = False
For Each WSheet In Worksheets
    Sheets(WSheet.Name).Select
    If Cells(1, 1).Value = "Test" Then    'you could add other tests here to make sure you only put charts on the sheets you want.
        X = 2
        Y = 7
        For Z = 1 To 4
            DataArray(1, Z) = Cells(X, Y + Z - 1).Value
        Next
        X = 3
        Do While True
            If Cells(X, 1).Value = "Total" Then
                Exit Do
            End If
            X = X + 1
        Loop
        Range("G2:J2,G" & X & ":J" & X).Select
        [B]ActiveSheet.Shapes.AddChart.Select[/B]
        ActiveChart.ChartType = xlPie
        Range("M8").Select
    End If
Next
 
End Sub

Another forum member suggested changing the bold to the code below, but this failed as well.

        Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, _
                                     Name:=ActiveSheet.Name

I have found that I can skip over the error, but instead of placing the charts on each of the sheets they are attributed to, the charts are placed on their own separate sheet.

 

[LNG2013]

FIXED!

Found this article here and figured out to use a DIM
http://www.mrexcel.com/forum/showthread.php?p=2962249

BOLD is the modifications. Make sure to give the sShtName its representation inside the For as the For selects the sheets.

Sub CreatePieChart()
Dim X, Y As Long
Dim DataArray(2, 4) As Variant
Dim WSheet As Worksheet
 
[B]Dim sShtName As String[/B]
Application.ScreenUpdating = False
For Each WSheet In Worksheets
    Sheets(WSheet.Name).Select
[B]    sShtName = ActiveSheet.Name
[/B]    If Cells(1, 1).Value = "Test" Then    'you could add other tests here to make sure you only put charts on the sheets you want.
     'On Error Resume Next
        X = 2
        Y = 7
        For Z = 1 To 4
            DataArray(1, Z) = Cells(X, Y + Z - 1).Value
        Next
        X = 3
        Do While True
            If Cells(X, 1).Value = "Total" Then
                Exit Do
            End If
            X = X + 1
        Loop
        Range("G2:J2,G" & X & ":J" & X).Select
                Charts.Add
[B]        ActiveChart.Location Where:=xlLocationAsObject, Name:=sShtName
[/B]        ActiveChart.ChartType = xlPie
        Range("M8").Select
    End If
Next
 
End Sub