VBA & Pivot Tables

A

ai1094

Board Regular
Joined
Aug 23, 2018
Messages
92
Hello, I had a question regarding VBA and Pivot Tables. How can I reference (in VBA) the current sheet? For example, my macro only recognizes "Sheet2" when I create a Pivot Table for my data. When I delete that sheet and run my macro again to create a new pivot table on a new sheet, it becomes "Sheet3" and the code won't run because it's referencing "Sheet2". So again, my question is, how can I tell my code that no matter what sheet number it is, it'll always reference the Active (current) sheet?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Here is my code:

Code: 
Sub Pivot1()


Dim wsNew As Worksheet


Set wsNew = ThisWorkbook.Sheets.Add(Before:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))


Application.ScreenUpdating = False


             
'
' Pivot1 Macro
'


'
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R221674C115", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="" & wsNew.Name & "!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("BUILDING_LOCID")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("OPTION_CODE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("OPTION_STATUS_NAME")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("LINE_ITEM_TYPE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("LINEITEM")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("C_YEAR")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("ACTUALS_RO"), "Sum of ACTUALS_RO", xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ACTUALS_RO")
        .NumberFormat = "$#,##0.00"
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("OPTION_CODE").CurrentPage _
        = "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("OPTION_CODE")
        .PivotItems("[NULL]").Visible = False
        .PivotItems("A").Visible = False
        .PivotItems("B").Visible = False
        .PivotItems("C").Visible = False
        .PivotItems("D").Visible = False
        .PivotItems("E").Visible = False
        .PivotItems("F").Visible = False
        .PivotItems("P").Visible = False
        .PivotItems("R").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("OPTION_CODE"). _
        EnableMultiplePageItems = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("OPTION_STATUS_NAME"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("OPTION_STATUS_NAME"). _
        CurrentPage = "Forecast"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("LINE_ITEM_TYPE"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("LINE_ITEM_TYPE"). _
        CurrentPage = "P&L"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("LINEITEM")
        .PivotItems("CS Total - P&L").Visible = False
    End With
    Sheets("Sheet1").Select
    ActiveWindow.WindowState = xlMaximized
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable1").PivotCache. _
        CreatePivotTable TableDestination:="Sheet2!R25C1", TableName:="PivotTable2" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(25, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("BUILDING_LOCID")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("OPTION_CODE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("LINE_ITEM_TYPE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("OPTION_STATUS_NAME")
        .Orientation = xlPageField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("LINEITEM")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("C_YEAR")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("ACTUALS_RO"), "Sum of ACTUALS_RO", xlSum
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of ACTUALS_RO")
        .NumberFormat = "$#,##0.00"
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("LINEITEM")
        .PivotItems("CS Total - P&L").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable2").PivotFields("OPTION_CODE"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields("OPTION_CODE").CurrentPage _
        = "ACT"
    ActiveSheet.PivotTables("PivotTable2").PivotFields("OPTION_STATUS_NAME"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields("OPTION_STATUS_NAME"). _
        CurrentPage = "Forecast"
    ActiveSheet.PivotTables("PivotTable2").PivotFields("LINE_ITEM_TYPE"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields("LINE_ITEM_TYPE"). _
        CurrentPage = "P&L"
    ActiveSheet.PivotTables("PivotTable2").TableStyle2 = "PivotStyleLight17"
    ActiveWindow.SmallScroll Down:=9
    Sheets("Sheet1").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable2").PivotCache. _
        CreatePivotTable TableDestination:="Sheet2!R45C1", TableName:="PivotTable3" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(45, 1).Select
    Range("A46").Select
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("BUILDING_LOCID")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("OPTION_CODE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("OPTION_STATUS_NAME")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("LINE_ITEM_TYPE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("LINEITEM")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("C_YEAR")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("ACTUALS_RO"), "Sum of ACTUALS_RO", xlSum
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Sum of ACTUALS_RO")
        .NumberFormat = "$#,##0.00"
    End With
    ActiveSheet.PivotTables("PivotTable3").PivotFields("OPTION_CODE"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable3").PivotFields("OPTION_CODE").CurrentPage _
        = "RO"
    ActiveSheet.PivotTables("PivotTable3").PivotFields("OPTION_STATUS_NAME"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable3").PivotFields("OPTION_STATUS_NAME"). _
        CurrentPage = "Forecast"
    ActiveSheet.PivotTables("PivotTable3").PivotFields("LINE_ITEM_TYPE"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable3").PivotFields("LINE_ITEM_TYPE"). _
        CurrentPage = "P&L"
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("LINEITEM")
        .PivotItems("CS Total - P&L").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable3").TableStyle2 = "PivotStyleLight18"
    ActiveWindow.SmallScroll Down:=24
    Sheets("Sheet1").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable3").PivotCache. _
        CreatePivotTable TableDestination:="Sheet2!R62C1", TableName:="PivotTable4" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(62, 1).Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("BUILDING_LOCID")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("OPTION_CODE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("OPTION_STATUS_NAME")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("LINE_ITEM_TYPE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("LINEITEM")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("C_YEAR")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("ACTUALS_RO"), "Sum of ACTUALS_RO", xlSum
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum of ACTUALS_RO")
        .NumberFormat = "$#,##0.00"
    End With
    ActiveSheet.PivotTables("PivotTable4").PivotFields("OPTION_CODE"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable4").PivotFields("OPTION_CODE").CurrentPage _
        = "AUTO RENEWAL"
    ActiveSheet.PivotTables("PivotTable4").PivotFields("OPTION_STATUS_NAME"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable4").PivotFields("OPTION_STATUS_NAME"). _
        CurrentPage = "Forecast"
    ActiveSheet.PivotTables("PivotTable4").PivotFields("LINE_ITEM_TYPE"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable4").PivotFields("LINE_ITEM_TYPE"). _
        CurrentPage = "P&L"
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("LINEITEM")
        .PivotItems("CS Total - P&L").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable4").TableStyle2 = "PivotStyleLight19"
    Sheets("Sheet1").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable4").PivotCache. _
        CreatePivotTable TableDestination:="Sheet2!R79C1", TableName:="PivotTable5" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(79, 1).Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("BUILDING_LOCID")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("OPTION_CODE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("OPTION_STATUS_NAME")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("LINE_ITEM_TYPE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("LINEITEM")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("C_YEAR")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("FORECAST"), "Count of FORECAST", xlCount
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Count of FORECAST")
        .Caption = "Sum of FORECAST"
        .Function = xlSum
        .NumberFormat = "$#,##0.00"
    End With
    ActiveSheet.PivotTables("PivotTable5").PivotFields("OPTION_CODE").CurrentPage _
        = "(All)"
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("OPTION_CODE")
        .PivotItems("ACT").Visible = False
        .PivotItems("AUTO RENEWAL").Visible = False
        .PivotItems("RO").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable5").PivotFields("OPTION_CODE"). _
        EnableMultiplePageItems = True
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("LINEITEM")
        .PivotItems("CS Total - P&L").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable5").TableStyle2 = "PivotStyleLight15"
    ActiveWindow.SmallScroll Down:=-72
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
End Sub
 
Last edited by a moderator:
Upvote 0
Sorry, I have no simple answer.

It looks like you have a jumble of code that was created recording a macro. If you can point to a specific line you need help with, I can help -- otherwise, there's almost certainly someone else here smarter than me.

Sorry :(
 
Upvote 0
You must log in or register to reply here.

Similar threads

zookeepertx
  • Question
Macro will run correctly in 1 workbook but not another
Replies
5
Views
95
zookeepertx
zookeepertx
S
  • Solved
VBA to create pivot tables
2
Replies
12
Views
232
Domenic
D
tezza
  • Question
VBA Pivot Table with multiple fields
Replies
0
Views
23
tezza
tezza
S
  • Question
Pivot Table doesn't get new data
Replies
4
Views
142
RoryA
RoryA
M
  • Question
VBA activate sheets in separate windows
Replies
1
Views
114
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,203,461
Messages
6,055,559
Members
444,798
Latest member
PAO1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top