Macro that won't work on all sheets

[montanaheather]

Hello,

I am trying to write a macro for a pivot table that will work on all sheets in my workbook. Each sheet represents a different month, and the pivot table is meant to summarize budget information for each month. Below is the code. Please help!

Sub BudgetMacro1()
'
' BudgetMacro1 Macro
'
'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R35C5", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet1!R4C9", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(4, 9).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
End Sub

 

[Jaye7]

Did you try changing the sheet1 value to activesheet

 

[montanaheather]

Yeah, but I am not sure how to replace it, I think. How do I handle the "" marks around Sheet1? See below.

Sub BudgetMacro1()
'
' BudgetMacro1 Macro
'
'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"activesheet!R1C1:R35C5", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="activesheet!R4C9", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12
Sheets("activesheet").Select
Cells(4, 9).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")
.Orientation = xlColumnField
.Position = 1
End With

 

[Jaye7]

Sorry, this is not as easy as it looks, I tried recording a script and it will not playback, I searched the internet for a while and there were no solutions that I could find.

I hope that someone else can help you.

 

[AlphaFrog]

Sub BudgetMacro1()
    '
    ' BudgetMacro1 Macro
    '
    '
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=[COLOR="Red"]Range("A1:E35")[/COLOR], _
                                      Version:=xlPivotTableVersion12).CreatePivotTable _
                                      TableDestination:=[COLOR="Red"]Range("I4")[/COLOR], TableName:="PivotTable1", _
                                      DefaultVersion:=xlPivotTableVersion12
    
    Cells(4, 9).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1
    End With
End Sub

No need to reselect the Active sheet.

 

[montanaheather]

Thank you! That worked like a charm!