Create Pivot Table Based on Dynamic Range

[keen1598]

So i have a worksheet with a small table on it using columns A:O. Using a starting point of cell B2 and then-- Cells.Find(What:="*", After:=[b2], SearchDirection:=xlPrevious).Select -- I am able to select the range in which I'd like to use for the pivot table. I've repeatedly tried to name this range by setting the currentregion.name to a string or a range, and every time i get an empty variable. Ive tried using activecell.currentregion to create a name using the Names.Add Name:= and still nothing

if anyone can help me figure this out id be much obliged. here is my code so far using excel 2007:Dim unit(1 To 2, 1 To 26) As String
Dim unitx As Integer
Dim unity As Integer
Dim rownum As Integer
Dim pt As PivotTable
Dim pi As PivotItem
Dim AllData As Range
Application.ScreenUpdating = True
Windows("CLABSI QDM.xls").Activate
Application.ScreenUpdating = True
Cells.Find(What:="*", After:=[b2], SearchDirection:=xlPrevious).Select
Range("B2", ActiveCell).Select
ActiveWorkbook.ActiveSheet.Names.Add Name:="TBL1", RefersTo:=Range("B2", ActiveCell).Select
MsgBox AllArea
MsgBox TBL1
Sheets.Add
Sheets("Sheet1").Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=TBL1, _
Version:=xlPivotTableVersion10).CreatePivotTable TableDestination:="Sheet1!R3C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

Sheets("Sheet1").Select
Cells(3, 1).Select
​