Having trouble with pivot table macro

[SCPbrito]

I just recorded this macro and when I try to run it I'm getting an error. I'm having a hard time troubleshooting this.

Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Item_Transaction!R1C1:R4709C30", Version:=8).CreatePivotTable _
        TableDestination:="Pivot Table!R1C1", TableName:="PivotTable1", _
        DefaultVersion:=8
    Sheets("Pivot Table").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Time")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Time").AutoGroup
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Quantity"), "Sum of Quantity", xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("User ID")
        .Orientation = xlRowField
        .Position = 1
    End With
End Sub

 

[SCPbrito]

OK, im not by my computer now ill try that. I was thinking the same i just wanted to confirm. I would also need to update where time column is located on new file

 

[SCPbrito]

OK so I modified my code a bit. When I run this now I get error "object required" and the first line "With wsItem_Transaction.Sort" is highlighted in debugger.

With wsItem_Transaction.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=wsItem_Transaction.Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange wsItem_Transaction.Range("$A$1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    wsItem_Transaction.Columns("E:E").NumberFormat = "[$-en-US]m/d/yy h:mm AM/PM;@"

    ' Find the last row with data in column B
    lastRow = wsItem_Transaction.Cells(wsItem_Transaction.Rows.Count, "E").End(xlUp).Row

    ' Get the current date and time
    currentTime = Now
    ' Set the cutoff time for the current day at 6:50 AM
    cutoffTime = DateValue(currentTime) + TimeValue("06:50:00")

    ' Loop from the last row to the first row (bottom-up)
    For i = lastRow To 1 Step -1
        ' Check if the timestamp in column B is before the cutoff time
        If IsDate(wsItem_Transaction.Cells(i, 2).Value) Then
            If wsItem_Transaction.Cells(i, 2).Value < cutoffTime Then
                ' Delete the row if the condition is met
                wsItem_Transaction.Rows(i).Delete
            End If
        End If
    Next i

    Application.ScreenUpdating = True

    MsgBox "Macro complete!"
End Sub

 

[RoryA]

The only change you should have made to the code was the line I posted. All the other lines using ws remain as they are.