HELP.. PLEASE Macro for Pivot table

[shaunkaz]

Hi All..

I have a macro which creates a macro on sheet 1.. what I want is if Im on sheet 2 I want to run the same macro and input the macro for that sheet only and same again if Im on sheet 3 etc..

Here is my macro:

how do I modify.. Thanks


Sub Macro4()
'
' Macro4 Macro
'

'
Range("A2:D200").Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R2C1:R200C4", Version:=6).CreatePivotTable TableDestination:= _
"Sheet1!R3C8", TableName:="PivotTable3", DefaultVersion:=6
Sheets("Sheet1").Select
Cells(3, 8).Select
With ActiveSheet.PivotTables("PivotTable3")
.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("PivotTable3").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable3").RepeatAllLabels xlRepeatLabels
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Route covered ")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("£ AM "), "Count of £ AM ", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("£ PM "), "Count of £ PM ", xlCount
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of £ AM ")
.Caption = "Sum of £ AM "
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of £ PM ")
.Caption = "Sum of £ PM "
.Function = xlSum
End With
ActiveWorkbook.ShowPivotTableFieldList = False

End Sub

 

[Domenic]

Try removing the sheet reference from the table destination . . .

TableDestination:="R3C8"

Hope this helps!

 

[shaunkaz]

Thank you it works

 

[Domenic]

You're very welcome, cheers!