Pivot Table VBA Code

[bmkelly]

Hello,

I had followed along with the code in this link Automate Your Pivot Table With VBA [Step By Step Guide] and edited the code to match my data and I can't seem to get it to work. It currently runs thru with no errors but also just creates the PivotTable Sheet and then just leaves the sheet blank.

Here is my code:

Sub PT()

'Declare Variables'
    Dim pws As Worksheet
    Dim tws As Worksheet
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    Dim PRange As Range
    Dim LastRow As Long
    Dim LastCol As Long

        On Error Resume Next
    
            Application.DisplayAlerts = False
                Worksheets("PivotTable").Delete
                Sheets.Add Before:=ActiveSheet
                ActiveSheet.Name = "PivotTable"
            Application.DisplayAlerts = True
                Set pws = Worksheets("PivotTable")
                Set tws = Worksheets("Transactions")
        
'Define Data Range'
    LastRow = tws.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = tws.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = tws.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache'
    Set PCache = ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
        TableName:="Table1")

'Insert Blank Pivot Table'
    Set PTable = PCache.CreatePivotTable _
        (TableDestination:=pws.Cells(1, 1), TableName:="Table1")

'Insert Row Fields'
    With ActiveSheet.PivotTables("Table1").PivotFields("RSQM_Cust_Name")
        .Orientation = xlRowField
        .Position = 1
    End With

'Insert Data Field'
    With ActiveSheet.PivotTables("Table1").PivotFields("Equipment ID")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlCount
        .Name = "Device Count"
    End With

    With ActiveSheet.PivotTables("Table1").PivotFields("BDS Unit Price")
        .Orientation = xlDataField
        .Position = 2
        .Function = xlSum
        .NumberFormat = "#,##0"
        .Name = "Sum of BDS Unit Price"
    End With
    
    With ActiveSheet.PivotTables("Table1").PivotFields("FY22 Impact")
        .Orientation = xlDataField
        .Position = 3
        .Function = xlSum
        .NumberFormat = "#,##0"
        .Name = "Sum of FY22 Impact"
    End With

'Format Pivot'
    TableActiveSheet.PivotTables("Table1").ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("Table1").TableStyle2 = "PivotStyleLight16"

End Sub

Here is what I would like the Pivot Table to look like:

FY22 Q2 Q3 (Jul-Dec 21) UNC Hospital Pricing Doc - SM Review.xlsx
	A	B	C	D
3	Row Labels	Sum of BDS Unit Price	Sum of FY22 Impact	Device Count
4	UNC Hospitals-CTS	$ 50,718.00	$ 58,355.03	4746
5	UNC Rockingham Healthcare	$ (2,017.48)	$ 7,473.66	423
6	UNC P & A Clinics-CTS	$ (78,699.21)	$ (53,855.30)	398
7	UNC Physician Network	$ (12,513.85)	$ (9,480.65)	306
8	Chatham Hospital-CTS	$ 154.17	$ 2,068.03	132
9	UNC Campus Health Services-CTS	$ (25,935.97)	$ (22,876.72)	105
10	UNC DIABETES CARE CENTER	$ -	$ -	2
11	Grand Total	$ (68,294.34)	$ (18,315.96)	6112
Pivot

 

[bmkelly]

You're welcome.

I did come across an issue (for future years/and updated templates) so we have FY (Fiscal Year), CY (Calendar Year) and OY (Operating Year) years with different templates...Some may say FY21, FY22 etc, some may be CY21, CY22 etc and others OY21, OY22 etc just depending on the year of the Hospital...Is there a way to have this code read just the FY, CY or OY part of things or will I have to basically update the macro each year for each Accounting Year?

 

[bmkelly]

I did come across an issue (for future years/and updated templates) so we have FY (Fiscal Year), CY (Calendar Year) and OY (Operating Year) years with different templates...Some may say FY21, FY22 etc, some may be CY21, CY22 etc and others OY21, OY22 etc just depending on the year of the Hospital...Is there a way to have this code read just the FY, CY or OY part of things or will I have to basically update the macro each year for each Accounting Year?

Like I just ran this code on one of my CY accounts and due to the code showing FY22 Impact it didn't find that in my spreadsheet since my spreadsheet is CY** (the year will change)

 

[RoryA]

You could have it read the FY22 (or whatever) from a cell, or have it pop up an input box to enter the relevant name?

 

[bmkelly]

You could have it read the FY22 (or whatever) from a cell, or have it pop up an input box to enter the relevant name?

Okay - after doing some digging and a chat with co-workers I think we are just going to simplify our lives by just having that column header "Effective Price" in the templates that way we don't have to have multiple codes reading each Accounting Type and Accounting Year. Thank you!

 

[jiwleini]

Try something like this:

Sub PT()

'Declare Variables'
    Dim pws As Worksheet
    Dim tws As Worksheet
    Dim PCache As PivotCache
    Dim PTable As PivotTable
  
   Application.DisplayAlerts = False
   On Error Resume Next
   Worksheets("PivotTable").Delete
   On Error GoTo 0
   Application.DisplayAlerts = True
  
   Set pws = Sheets.Add(Before:=ActiveSheet)
   pws.Name = "PivotTable"
  
   Set tws = Worksheets("Transactions")
      
'Define Data Range'
' use the region from A1
' pivot tables work best with an address in R1C1 format, and the sheet name should be enclosed in single quotes in case of spaces/special characters
    Dim dataAddress As String
   dataAddress = "'" & tws.Name & "'!" & tws.Cells(1, 1).CurrentRegion.Address(ReferenceStyle:=xlR1C1)

'Define Pivot Cache'
   Set PCache = ActiveWorkbook.PivotCaches.Create _
       (SourceType:=xlDatabase, SourceData:=dataAddress)

'Insert Blank Pivot Table'
   Set PTable = PCache.CreatePivotTable _
       (TableDestination:=pws.Cells(1, 1), TableName:="Table1")
      
' hold a reference to the pivot table we just created
   With PTable
  
   'Insert Row Fields'
      With .PivotFields("RSQM_Cust_Name")
          .Orientation = xlRowField
          .Position = 1
      End With

   'Insert Data Fields'
      With .PivotFields("Equipment ID")
          .Orientation = xlDataField
          .Position = 1
          .Function = xlCount
          .Name = "Device Count"
      End With

      With .PivotFields("BDS Unit Price")
          .Orientation = xlDataField
          .Position = 2
          .Function = xlSum
          .NumberFormat = "#,##0"
          .Name = "Sum of BDS Unit Price"
      End With
  
      With .PivotFields("FY22 Impact")
          .Orientation = xlDataField
          .Position = 3
          .Function = xlSum
          .NumberFormat = "#,##0"
          .Name = "Sum of FY22 Impact"
      End With

'Format Pivot'
      .ShowTableStyleRowStripes = True
      .TableStyle2 = "PivotStyleLight16"
     
   End With

End Sub

Any problems or questions about the code, please post back.

Thank you very much for the solution.

 

[RoryA]

Okay - after doing some digging and a chat with co-workers I think we are just going to simplify our lives by just having that column header "Effective Price" in the templates that way we don't have to have multiple codes reading each Accounting Type and Accounting Year. Thank you!

Simple is always good!