Edit Macro Code to reference law row & last column on a tab instead of a fixed reference

[VorLag]

Hello, fellow Excel-geeks!

I am trying to get a Macro working to create a pivot table for me, but I am having trouble with getting it to look for whatever the last column and last row are. I have been poking around online and have tried a few different things, but I either don't understand the code that those other sites are using and I'm using it wrong, or there is a different method that I need to use.

So, my worksheet has 2 tabs - Details, with all of the raw data, and Pivot, which has nothing right now, but should have the pivot table that references the Details tab. My data on the Details tab begins at Cell A6 and in this set of data, it ends with the last cell of Column AC. However, the number of Columns may change and the number of Rows will definitely change.

I can live with it always starting with A6, because that part is easy enough to change if/when needed.

So, I found the first part of this code elsewhere online and put it in. When I try to run this Macro, I get this error message:

Unable to get the PivotTables property of the Worksheet class

I have no idea what that means.

Here is the code for my macro:

    ActiveWorkbook.Worksheets("Details").Range("A6").Select
    Selection.CurrentRegion.Select
    DataArea = "Details!R6C1:R" & Selection.Rows.Count + 5 & "C" & Selection.Columns.Count
 
    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
        Version:=xlPivotTableVersion14)

    Sheets("Pivot").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Duplicate")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Job Category")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Job Status")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("SLA Time to Shortlist" _
        )
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Job ID"), "Sum of Job ID", xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Job ID")
        .Caption = "Count of Job ID"
        .Function = xlCount
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Duplicate").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Duplicate")
        .PivotItems("Duplicate").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Duplicate"). _
        EnableMultiplePageItems = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Job Category").CurrentPage _
        = "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Job Category")
        .PivotItems("Call Center").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Job Category"). _
        EnableMultiplePageItems = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Job Status").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Job Status")
        .PivotItems("Canceled").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Job Status"). _
        EnableMultiplePageItems = True
End Sub

ALSO,

Does anyone know how I might add some conditional language into the actual pivot table code? For example, it is expected that there will always be Duplicates, so no condition is needed there, but there may not be any Call Center data or there may not be any Canceled jobs in the data.

I get an error in the code if that is the case, but I need the Macro to always filter Call Center and Canceled jobs out of the pivot table IF they exist in the data. How can I do that?