Thread: changing range selection for pivot table based on worksheet

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

Hi,

A simple solution for you is to name your ranges and use Dynamic ranges ... i.e ranges which adapt themeselves to the database ...

Should you need some explanation ...
http://www.contextures.com/xlNames01.html#Dynamic

HTH

that sounds good, my only problem, is that the names and number of worksheets that I will be cycling through will vary from time to time, is there a way to make the dynamic range one that will automatically change for different worksheets rather than defining one range for each of the sheets?

Hi,

If you have a great deal of worksheets and database ranges, a macro can automatically generate all the Range Names ...

HTH

It ended up being even easier than that, i just defined a variable as the last row, and set the source data equal to

SourceData:=ActiveSheet.Range("$A$2:H" & lastRow)

and the lastRow variable changes with each worksheet

Thanks for your help