You could use the current region of cell A3. For example:

'=========================
Dim ws As Worksheet
Dim rng As Range
Set ws = Sheets("Sheet1")
Set rng = ws.Range("A3").CurrentRegion

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=ws.Name & "!" & rng.Address) _
.CreatePivotTable TableDestination:="", _
TableName:="PivotTable1"
'======================

solomon_monkey wrote:
Can I write a macro for a pivot table defining the range as

Range("A3:K3", Selection.End(xlDown)).Select

I'm trying with

Dim rng As Range
Range("A3:K3", Selection.End(xlDown)).Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"rng").CreatePivotTable TableDestination:= _
"'[Combined Filtered.xls]Pivot'!R5C1",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
And it tells me 'Nooooooooooo'.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html