Help With Code

[Desu Nota from Columbus]

I have the following code where everything works except for the pivot table.

The pivot table is not created, I think this is because I don't have the Table Destination correct (it is asking for me to specify the [workbook] but i'm not sure how to say "this workbook"). Also, I have a dynamic pivot table source data (the amount of rows change, but the columns DO NOT). How would I set the source range to select all rows with data?

This macro runs within one workbook (with multiple sheets) and I am taking data from sheet16, and putting a pivot table on sheet Results! F3.

With Sheets("Sheet16")
    Columns("A:N").NumberFormat = "General"
    Range("A:A,G:G,L:L").NumberFormat = "m/d/yyyy h:mm"
    Range("D:D,H:J,M:N").Delete
    Columns("F:F").Select
    Selection.Cut
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Columns("H:H").Select
    Selection.Cut
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Columns("G:G").Select
    Selection.Cut
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight
    Cells.Select
    Selection.Columns.AutoFit
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Value = "CaptureTime"
    Range("B1").Value = "DeviceKey"
    Range("C1").Value = "Start Time"
    Range("D1").Value = "End Time"
    Range("E1").Value = "Duration"
    Range("F1").Value = "Reason"
    Range("G1").Value = "State"
    Range("H1").Value = "Sequence Number"
    Range("A1,B1,C1,D1,E1,F1,G1,H1").Select
            With Selection.Font
                .FontStyle = "Bold"
            End With
    Cells.AutoFilter
    Selection.AutoFilter Field:=7, Criteria1:="run_enum"
    Columns("A:A").Select
    Range("A" & Rows.Count).End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
    On Error Resume Next
    Selection.AutoFilter Field:=7, Criteria1:="standby_enum"
    Columns("A:A").Select
    Range("A" & Rows.Count).End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
    On Error Resume Next
    Selection.AutoFilter Field:=7, Criteria1:="setup_enum"
    Columns("A:A").Select
    Range("A" & Rows.Count).End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
    On Error Resume Next
    Selection.AutoFilter Field:=7, Criteria1:="offline_enum"
    Columns("A:A").Select
    Range("A" & Rows.Count).End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
    On Error Resume Next
    Cells.AutoFilter
    Range("BB1").Value = 86400
    Range("BB1").Copy
    Range("E2:E65534").PasteSpecial xlPasteValues, xlPasteSpecialOperationDivide
    Columns("E:E").NumberFormat = "[h]:mm:ss"
    Columns("BB:BB").Delete
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet16!R1C1:R152C9").CreatePivotTable TableDestination:= _
        "Results!R4C6", TableName:="PivotTable1", DefaultVersion:= _
        xlPivotTableVersion10
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Reason", _
        ColumnFields:="DeviceKey"
End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Duration")
        .Orientation = xlDataField
        .Caption = "Sum of Duration"
        .Function = xlSum
        .NumberFormat = "[h]:mm:ss"
    End With

Ive posted code above the area of interest so you could see how the With statements are setup (if they are the problem).

The problem areas are in BOLD BLUE.

Thanks in advance.

 

[Desu Nota from Columbus]

bump...

 

[Desu Nota from Columbus]

I solved my own problem using a 5 step work-around...