Macro to Loop Through All Sheets

[sharpm80]

Hi I have a data set which contains hundreds of cities and I want to run a pivot on each city and export each city's pivot to a new workbook. To accomplish this I ran a Macro to break each cities data out into its own sheet. Now for each city I would like to insert blank rows above the data and insert a pivot table into those blank rows. I have listed the code below to create the pivot but I am having trouble looping it through all the worksheets.



Sub Macro4()
'
' Macro4 Macro
'


Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String


Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown


'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A26:t9000").Address(ReferenceStyle:=xlR1C1)


'Where do you want Pivot Table to start?
StartPvt = ActiveSheet.Name & "!" & ActiveSheet.Range("A1").Address(ReferenceStyle:=xlR1C1)


'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)


'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable")


End Sub

 

[Fluff]

Deleted, after seeing post#10

 

[sharpm80]

Hey Fluff, there is something wrong with my pivot code, because it does not work in every sheet for some reason, so I am going to create a new thread specifically for the pivot. Thanks for helping with the code though I am going to use it in the new thread if that is alright.

 

[Fluff]

That's fine & good luck