excel macro

[Allan]

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:=xlDatabase,
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

 

[Carim]

Hi Allan,

If I undertand correctly, your problem can be solved with dynamic range
....
http://www.contextures.com/xlNames01.html#Dynamic

HTH
Cheers
Carim