Create dynamic range, then pivot table VBA

[gabbana]

Hello all,

I'm having a bit of an issue creating a pivot table from a dynamic range. The problem I have is the data for the range is itself is dynamically generated from another filtered (huge) worksheet which changes everyday.

I can get the first sheet to open, filter, and copy all the relevant required info to a new sheet, but due to this, that sheet is, in itself new, and dynamic. (I think) I can't just use the original sheet due to file size limits for e-mails.

So, I think I somehow need to then create a range/table from the data on the new sheet, before creating the pivot. I've tried searching for an answer but can't find anything that fits.

The pivot table is reasonably simple:

Sub VariablePivot()

    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R46214C127", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("offcat")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("offcat"), "Count of offencecat", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("area")
        .Orientation = xlRowField
        .Position = 1
    End With
End Sub

I'm struggling to work out how to change the range to make it dynamic for the sheet it's on.