Excel 2003
VB 6.5

My original spreadsheet has two worksheets with the following names:
The first sheet is "June 2008 to date".
The second sheet is "July 2007 to date".

I recorded a macro (see below) that produces a pivot table and pivot chart using the data on the second sheet, "July 2007 to date".

After recording I see two new sheets:
A sheet labeled "Sheet1" inserts itself to the left of the sheet "July 2007 to date' and to the left of that sheet is one called "Chart1".

Range("A2").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'July 2007 to date'!R1C1:R884C10").CreatePivotTable TableDestination:="", _
        TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Qty"), "Sum of Qty", xlSum
    Range("A4").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, True, False, True)
    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A4")
    ActiveChart.Location Where:=xlLocationAsNewSheet
    Application.CommandBars("PivotTable").Visible = False
    ActiveWorkbook.ShowPivotTableFieldList = False
When I run the macro again I get a "Subscript out of range" message. I noticed that it produced two more sheets to the left of "Sheet1" and "Chart1", i.e. "Sheet2" and "Chart2".

Since the line 
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A4")
is fixed, for now, it needs to be generalized. Or perhaps do I need to query the program at the beginning to delete any previously created "sheets" and "charts".

Need help for that part of the code.

Thanks