ExcelBanter - excel macro

I have 3 worksheets in a workbook:
1. datasht - contains data
2. tablesht - will contain table
3. loadsht - contains two elements
a. cell A1 contains name and data range for 1. such
as DATASHT!R1C1:R497C5
b. a macro that will read cell A1value and then
create a pivot table in TABLESHT

Below is the code I recorded by using Excel-Macro-Record a New Marco
funtion, then I made change in order to read different lenght of data
to create pivot table in the certain worksheet , but it does not work.
Could some one help? Thank you! Allan

Public Sub Pivottbl()

DIM DATAPATH As String
DATAPATH = RANGE("LOADSHT!A1")

Sheets(TABLESHT).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
DATAPATH).CreatePivotTable TableDestination:=Range("A3"),
TableName _
:="PivotTable1"

ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array(
_
"Group", "SubGroup", "Data"), ColumnFields:="Classes"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count")
.Orientation = xlDataField
.Caption = "Freq"
End With
ActiveWorkbook.Save
End Sub

Th