Hi everyone, I am trying to execute a macro for formatting some data I have as well as creating a workbook. If I am being honest, I do not have much experience with macros and used chatgpt as a reference. Chatgpt gave me the below code, which did work at first, but now I am having the error I mentioned above. The purpose of this macro is to do some formatting first, then rename that sheet to whatever today's date is "mmddyy." Then it should add a new sheet and name it "Data Pivot" which should then be moved to the beginning so it is first. At cell A1 there should be a pivot table which references the data in my previous sheet. My data is located at row 3 from cells A to I. I need my pivot table to select all filled cells from A3 down and up until column I. The issue I am having is that my macro is giving me an error once it is about to insert the pivot table at cell A1.
When I click debug this is where the error is:
Set pivotTbl = pivotWs.PivotTables.Add(PivotCache:=ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:=dataRange), TableDestination:=pivotWs.Cells(1, 1))
Please help!
This is my code:
Sub CreateReport()
Dim ws As Worksheet
Dim pivotWs As Worksheet
Dim pivotTbl As PivotTable
Dim dataRange As Range
Dim todaysDate As String
' Step 1: Delete column J
Columns("J:J").Delete
' Step 2: Add 2 new rows to the top
Rows("1:2").Insert Shift:=xlDown
' Step 3: Type "PB Charge Review by WQ" in cell A1
Range("A1").Value = "PB Charge Review by WQ"
' Step 4: Merge cells A1 and B1
Range("A1:B1").Merge
' Step 5: Bold cell A1 and make the font 12 pt
With Range("A1")
.Font.Bold = True
.Font.Size = 12
End With
' Step 6: Align the text in cell A1 to the left
Range("A1").HorizontalAlignment = xlLeft
' Step 7: Underline cells A3:I3 (headers)
Range("A3:I3").Font.Underline = xlUnderlineStyleSingle
' Step 8: Autofit columns A-I
Columns("A:I").AutoFit
' Step 9: Rename the sheet to today's date (mmddyy)
todaysDate = Format(Date, "mmddyy")
ActiveSheet.Name = todaysDate
' Step 10: Add a new sheet and move it to the beginning
Set pivotWs = Sheets.Add(Before:=Sheets(1))
' Step 11: Rename this new sheet to "Data Pivot"
pivotWs.Name = "Data Pivot"
' Step 12: Set the data range for the pivot table
Set ws = Sheets(todaysDate)
Set dataRange = ws.Range("A3:I" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
' Step 13: Create the pivot table in the new sheet
Set pivotTbl = pivotWs.PivotTables.Add(PivotCache:=ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:=dataRange), TableDestination:=pivotWs.Cells(1, 1))
' Step 14-19: Add fields to the pivot table and format them
With pivotTbl
' Add "Owning Area" to rows
.PivotFields("Owning Area").Orientation = xlRowField
' Add "Num of Chg Sess" to values as a sum
With .PivotFields("Num of Chg Sess")
.Orientation = xlDataField
.Function = xlSum
End With
' Add "Amt on Chg Rvw" to values as a sum
With .PivotFields("Amt on Chg Rvw")
.Orientation = xlDataField
.Function = xlSum
End With
' Add "Avg Svc Dt Age" to values as an average
With .PivotFields("Avg Svc Dt Age")
.Orientation = xlDataField
.Function = xlAverage
End With
' Add "Avg Age" to values as an average
With .PivotFields("Avg Age")
.Orientation = xlDataField
.Function = xlAverage
End With
End With
' Step 20: Format columns B, D, and E with custom number format to show dashes instead of zeros
pivotWs.Columns(2).NumberFormat = "#,##0;-#,##0;–"
pivotWs.Columns(4).NumberFormat = "#,##0;-#,##0;–"
pivotWs.Columns(5).NumberFormat = "#,##0;-#,##0;–"
' Step 21: Format column C as currency with no decimals
pivotWs.Columns(3).NumberFormat = "$#,##0"
End Sub
