Pivot Table Macro, please help!

[elhason]

Hello, I am seeking out the help of someone here.

I am utilizing the code attached to create a pivot table. All was working great, but now a week later I cannot get it to work at all. I constantly get Run-time error '1004'

I know its because a portion of my code is not being executed and then moves onto the next macro application but cant because its dependent on the previous code being completed (attached is what is not being executed) but I don't understand why. As mentioned, it was working great and no problems and tested several times.

Rows will dynamically change, but columns will stay the same. I have looked at my data source, and the only difference is the report now has more rows, but I built this out with the plan that would happen.

Would really appreciate someone's help to figure this out.

Sub PivotTable_Unbilled()
'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long


'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Summary"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Summary")
Set DSheet = Worksheets("Data")


'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)


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


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


With ActiveSheet.PivotTables("Summary").PivotFields("Client Name")
.Orientation = xlRowField
.Position = 2
End With


With ActiveSheet.PivotTables("Summary").PivotFields("Eng. Name")
.Orientation = xlRowField
.Position = 3
End With


With ActiveSheet.PivotTables("Summary").PivotFields("Eng. No")
.Orientation = xlRowField
.Position = 4
End With


With ActiveSheet.PivotTables("Summary").PivotFields("Manager")
.Orientation = xlRowField
.Position = 5
End With



With ActiveSheet.PivotTables("Summary").PivotFields("Exceeds 15 Months")
.Orientation = xlRowField
.Position = 6
End With


With ActiveSheet.PivotTables("Summary").PivotFields("New Comment")
.Orientation = xlRowField
.Position = 7
End With


'Insert Data Field
ActiveSheet.PivotTables("Summary").AddDataField ActiveSheet.PivotTables( _
        "Summary").PivotFields("Unbilled WIP"), "Total Unbilled", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Unbilled WIP")
        .Caption = "Total Unbilled"
        .Function = xlSum
    End With


ActiveSheet.PivotTables("Summary").TableStyle2 = "PivotStyleMedium9"
    
    ActiveSheet.PivotTables("Summary").PivotFields("Partner").Subtotals = _
        Array(False, True, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("Summary").PivotFields("Client Name").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("Summary").PivotFields("Eng. Name").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("Summary").PivotFields("Eng. No").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("Summary").PivotFields("Manager").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("Summary").PivotFields("Exceeds 15 Months").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False _
        )
    ActiveSheet.PivotTables("Summary").PivotFields("New Comment").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
        
    With ActiveSheet.PivotTables("Summary")
        .InGridDropZones = True
        .ShowValuesRow = False
        .RowAxisLayout xlTabularRow
    End With
    
ActiveSheet.PivotTables("Summary").InGridDropZones = False


    Range("A1") = "EMC Unbilled Detail"
    Range("A1").Font.Bold = True
    Range("A2") = "=TEXT(TODAY(),""MMMM"")&"" FY18 - ""&""Run ""&TEXT(TODAY(),""MM/DD/YY"")"
    Range("A1:A2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A2").Font.Bold = True
    Range("A2").Font.Italic = True
    Range("A3:H17").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Range("A2").End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select


    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Range("H18").Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    
    Range("A18").End(xlDown).Select
    ActiveSheet.PivotTables("Summary").PivotFields("Partner").ShowDetail = _
        False
    Columns("B:F").EntireColumn.AutoFit
End Sub