setting up a pivot table via macro?

[SteveM]

I want to build a macro which will produce a pivot table. The catch is the
number of rows in my data will vary. I started by recording a macro which
built the pivot table. I knew it would hard code the range, but thought I
could substitute a range afterwards.

Which brings me to the crux of my problem; how to get the range? I came
across “currentregion†which I thought would do the job for me. I ended up
with this:
Dim DataRange As Range

Application.Goto Reference:="R2C1"
Set DataRange = Selection.CurrentRegion.Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Trial'!DataRange").CreatePivotTable TableDestination:="" _
, TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.Next.Select

I would run it from the sheet containing my data. That didn’t work. I also
tried a more brute force method:
Dim DataRange As Range

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

numRows = Selection.Rows.Count
numCols = Selection.Columns.Count - 2
Set DataRange = ActiveCell.Range(Cells(1, 1), Cells(numRows, numCols))

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"DataRange").CreatePivotTable TableDestination:= _
"'[R43 Open CRs StartCycle2.txt]Counts'!R3C1",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"FeatureName", "State"), ColumnFields:="Severity"
ActiveSheet.PivotTables("PivotTable1").PivotFields("id").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False

But no go there either. I am doing something wrong in setting the range or
using the range name in the pivot piece.

Anyone have any suggestions for me to get this to work?

Thanks in advance.