Repeat a Pivot Macro on Specific Worksheets

[islandbird]

Hi there,

I need help with repeating this macro to create a pivot table on all but two worksheets in my workbook. What I have developed here works perfectly for creating the pivot table on the one sheet. Whenever I try looping it by excluding a few sheets I run into issues. Many thanks in advance for your help. The two worksheets that need to be excluded are titled "Dashboard" and "Downtime Report"

Sub CreatePivot()

Dim shtSource As Worksheet
Dim rngSource As Range, rngDest As Range
Dim pvt As PivotTable


Set shtSource = ActiveSheet
Set rngSource = shtSource.Range("A1").CurrentRegion
Set rngDest = ActiveSheet.Range("J9")

Set pvt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSource, _
Version:=xlPivotTableVersion12).CreatePivotTable _
(TableDestination:=rngDest, DefaultVersion:=xlPivotTableVersion12)

pvt.AddDataField pvt.PivotFields("date"), "Count of date", xlCount

With pvt.PivotFields("date")
.Orientation = xlRowField
.Position = 1
End With

With pvt.PivotFields("ex/inc")
.Orientation = xlPageField
.Position = 1
End With

With pvt.PivotFields("up/down")
.Orientation = xlPageField
.Position = 1
End With

pvt.PivotFields("ex/inc").ClearAllFilters
pvt.PivotFields("ex/inc").CurrentPage = _
"included"
pvt.PivotFields("up/down").ClearAllFilters
pvt.PivotFields("up/down").CurrentPage = _
"DOWN"

End Sub

[Hawkeye16]

Please use code tags when posting code. It would be nice to see what you tried that did not work as well. I believe you would use something like this though. (untested)


edit: This is a little redundant with the worksheet variables, you could get rid of one of them.

Please Login or Register  to view this content.