I've got some code that i need to go through every different worksheet in my workbook and create a pivot table. I recorded the macro to make the pivot table, but i don't know how to change the source data to be flexible. basically i need it to always be from row 2 column 1 to row (last row) column 8, so only the second row will change. also, I don't know how to change the worksheet source, so that it is always the active worksheet.

here is what i have so far:

Sub Macro7()
 
     Dim pc As PivotCache, pt As PivotTable
    Dim cht As Chart
    For i = 1 To Worksheets.Count
   If Sheets(i).Name <> "Sheet1" And Sheets(i).Name <> "Sheet2" And Sheets(i).Name <> "Sheet3" And Sheets(i).Name <> "Sheet4" And Sheets(i).Name <> "Sheet5" And Sheets(i).Name <> "Sheet6" And Sheets(i).Name <> "Sheet7" And Sheets(i).Name <> "Sheet8" And Sheets(i).Name <> "Sheet9" And Sheets(i).Name <> "Sheet10" Then
       Sheets(i).Activate
    Set pc = ActiveWorkbook.PivotCaches.create(SourceType:=xlDatabase, SourceData:= _
        "ICS-GSD-Account Management!R2C1:R1106C8", Version:=xlPivotTableVersion10)
   Set pt = pc.CreatePivotTable(TableDestination:=Sheets("ICS-GSD-Account Management").Cells(2, 9), _
       TableName:="", DefaultVersion:=xlPivotTableVersion10)
        
    
    Set cht = ActiveSheet.Shapes.AddChart.Chart
    With cht
        .SetSourceData Source:=ActiveSheet.Range("$I$2:$O$15")
        .ChartType = xlColumnClustered
    End With
    
     With ActiveSheet.PivotTables("").PivotFields("Date")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("").PivotFields("Time")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("").AddDataField ActiveSheet.PivotTables(""). _
        PivotFields("Handled"), "Sum of Handled", xlSum
    ActiveSheet.PivotTables("").AddDataField ActiveSheet.PivotTables(""). _
        PivotFields("Aband Within"), "Sum of Aband Within", xlSum
    With ActiveSheet.PivotTables("").DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("").AddDataField ActiveSheet.PivotTables(""). _
        PivotFields("Aband Diff"), "Sum of Aband Diff", xlSum
    ActiveSheet.PivotTables("").AddDataField ActiveSheet.PivotTables(""). _
        PivotFields("Dequeued"), "Sum of Dequeued", xlSum
   
    cht.ChartType = xlBarStacked
    ActiveWorkbook.ShowPivotChartActiveFields = False
    ActiveWorkbook.ShowPivotTableFieldList = False
    cht.Legend.Select
   
    cht.Legend.LegendEntries(4).Select
    With cht.SeriesCollection(4)
    .Interior.ColorIndex = 44
   End With
    With cht.Parent
   .Width = 920
   .Height = 560
   .Left = 300
   .Top = 15

   End With
    With ActiveSheet.PivotTables("").PivotFields("Date")
        .PivotItems("2/1/2010").Visible = True
        .PivotItems("2/2/2010").Visible = False
        .PivotItems("2/3/2010").Visible = False
        .PivotItems("2/4/2010").Visible = False
        .PivotItems("2/5/2010").Visible = False
        .PivotItems("2/6/2010").Visible = False
        .PivotItems("2/7/2010").Visible = False
        .PivotItems("2/8/2010").Visible = False
        .PivotItems("2/9/2010").Visible = False
        .PivotItems("2/10/2010").Visible = False
        .PivotItems("2/11/2010").Visible = False
        .PivotItems("2/12/2010").Visible = False
        .PivotItems("2/13/2010").Visible = False
        .PivotItems("2/14/2010").Visible = False
        .PivotItems("2/15/2010").Visible = False
        .PivotItems("2/16/2010").Visible = False
        .PivotItems("2/17/2010").Visible = False
        .PivotItems("2/18/2010").Visible = False
        .PivotItems("2/19/2010").Visible = False
        .PivotItems("2/20/2010").Visible = False
        .PivotItems("2/21/2010").Visible = False
        .PivotItems("2/22/2010").Visible = False
        .PivotItems("2/23/2010").Visible = False
    End With
    ActiveSheet.PivotTables("").PivotFields("Date").EnableMultiplePageItems = True
     End If
Next i
 
End Sub
my main issues are coming at the lines:

  Set pc = ActiveWorkbook.PivotCaches.create(SourceType:=xlDatabase, SourceData:= _
        "ICS-GSD-Account Management!R2C1:R1106C8", Version:=xlPivotTableVersion10)
   Set pt = pc.CreatePivotTable(TableDestination:=Sheets("ICS-GSD-Account Management").Cells(2, 9), _
       TableName:="", DefaultVersion:=xlPivotTableVersion10)
and you can see that the sheet for source data is set, as well as the sheet for the pivot table (which i also don't know how to change to the active sheet)

any thing will help, thanks