How to create a Macro Pivot Table where the range changes?

[roxnoxsox]

I'm trying to create a macro which will create a pivot table out of columns A & B in my sheet 'Values0001' on a new sheet.

However, my number of rows will frequently change and I can't work out how to incorporate this into the macro. Does anyone have any suggestions? Here is my current code (with my attempted amendments):

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    
    Range("A1").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Sheets("Values0001").Range("A1:B" & LastRow), Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable3" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("ColumnAHeader")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("ColumnBHeader"), "Sum of ColumnBHeader", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False

[Aflatoon]

You could use this:

    Dim pc                    As PivotCache
    Dim pt                    As PivotTable




    With Sheets("Values0001")
        Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                SourceData:="'" & .Name & "'!" & .Range("A1").CurrentRegion.Resize(, 2).Address(ReferenceStyle:=xlR1C1), _
                Version:=xlPivotTableVersion14)
    End With
    Set pt = pc.CreatePivotTable(tabledestination:="", DefaultVersion:=xlPivotTableVersion14)
    ActiveWorkbook.ShowPivotTableFieldList = True
    With pt
        With .PivotFields("ColumnAHeader")
            .Orientation = xlRowField
            .Position = 1
        End With
        .AddDataField .PivotFields("ColumnBHeader"), "Sum of ColumnBHeader", xlSum
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False

[roxnoxsox]

This worked perfect! It seems to make sense as well so I think I can adjust this if ever needed again. Many thanks!