Macro Problem - PivotTable on multiple worksheets

[jakeangwin]

Hey,

I need help writing VBA code to run a macro in my worksheet.

I have about 60 sheets with data, the amount of data varies in each sheet but the format of the data is the same in every sheet. I want to create a macro that automatically creates a pivot for each sheet.

Below is the code of the macro I'm trying to run but I can't run the macro on the other sheets because I'm reference the sheet I created the macro on. How do I fix this?

Thank you.

Sub Rainfall_Macro()
'
' Rainfall_Macro Macro
'
' Keyboard Shortcut: Ctrl+r
'
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Dandaragan!R2C1:R25793C8", Version:=6).CreatePivotTable TableDestination:= _
"Dandaragan!R5C10", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Dandaragan").Select
Cells(5, 10).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Rainfall amount (millimetres)"), _
"Sum of Rainfall amount (millimetres)", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Year")
.Orientation = xlRowField
.Position = 1
End With
ActiveWindow.SmallScroll Down:=-24
End Sub