Cycle through worksheets vba

**twhitezell** · 04-02-2010, 12:48 PM

I'm working on a book, and I need to add a pivot chart to each worksheet in my workbook. I have everything set up right now to do what I need on one of the pages, but i'm clueless on where even to begin trying to get it to cycle through each worksheet, excluding the first 4 worksheets. I'm sure there is some kind of easy fix, but like i said, i've got no clue. This is what I have so far:

Sub Macro7()
 
     Dim pc As PivotCache, pt As PivotTable
    Dim cht As Chart
    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)
        
    Sheets("ICS-GSD-Account Management").Select
    Cells(2, 9).Select
    Set cht = ActiveSheet.Shapes.AddChart.Chart
    With cht
        .SetSourceData Source:=Sheets("ICS-GSD-Account Management").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 Sub

**davegugg** · 04-02-2010, 12:54 PM

You can cycle through like so:

For i = 1 To Worksheets.Count
    if sheets(i).name <> "WStoSkip" and sheets(i).name <> "OtherWStoSkip <> ... then
        Sheets(i).Activate
    
        'Your Code
    
    End If
Next i

Fill in the worksheets you want to skip (your first four) in the second line.

Does that work for you?

**twhitezell** · 04-02-2010, 01:04 PM

that gets the cycling to go, now in the code:

  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:=Sheets("ICS-GSD-Account Management").Range("$I$2:$O$15")
        .ChartType = xlColumnClustered
    End With

how can I set the source date just to the active sheet and not to a static sheet?

**davegugg** · 04-02-2010, 01:06 PM

Anywhere it references a sheet that you want it active instead of static, change it to ActiveSheet. instead of Sheets("...").

That should do it.

**twhitezell** · 04-02-2010, 01:15 PM

The only problem i get now is just with setting the pc variable

 Set pc = ActiveWorkbook.PivotCaches.create(SourceType:=xlDatabase, SourceData:= _
        "ICS-GSD-Account Management!R2C1:R1106C8", Version:=xlPivotTableVersion10)

where it gets the source data, it has a static reference, but I can't figure out how to work in the activesheet

**twhitezell** · 04-02-2010, 01:59 PM

Actually, the more I look at it, the more problems that seem to come up.

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)

Here is selects the data for the Pivot table, but i need to somehow get it to select row1column1:R.end(xlup)C8. you know what i'm saying? there are always going to be 8 columns, but the number of rows will vary by sheet (which i still can't figure out how to set it to change in the above code). but anyway, anything helps

**davegugg** · 04-05-2010, 10:47 AM

Unfortunately, I'm not very experienced working with PivotTables in VBA.
Does the set pc... line give you an error? If so, what is the error message? Is the SourceData:= argument changing based on what sheet you are on?

**twhitezell** · 04-05-2010, 10:52 AM

right now it works for the sheet ics-accountmanagement... but when it goes to the next sheet i get an error that says "run time error 1004" A PivotTable report with that name already exists on the destination sheet"

so it's trying to put it back on the same sheet, when i need it to go on the current active sheet

**twhitezell** · 04-05-2010, 10:59 AM

ok, actually, i've got part of it fixed

   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:=ActiveSheet.Cells(2, 9), _
       TableName:="", DefaultVersion:=xlPivotTableVersion10)

now I just have to figure out how to make the source data so that it will change for each worksheet, because now when it runs, i get the same graph on every page, but is there a way to define a range for the active worksheet that always selects a range that would be row2column 1 to row X column 8? where x is the last row of data?