Pivot Table

[solomon_monkey]

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:=xlDatabase, SourceData:= _
"rng").CreatePivotTable TableDestination:= _
"'[Combined Filtered.xls]Pivot'!R5C1",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
And it tells me 'Nooooooooooo'.

 

[Debra Dalgleish]

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:=xlDatabase, _
SourceData:=ws.Name & "!" & rng.Address) _
.CreatePivotTable TableDestination:="", _
TableName:="PivotTable1"
'======================

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:=xlDatabase, SourceData:= _
"rng").CreatePivotTable TableDestination:= _
"'[Combined Filtered.xls]Pivot'!R5C1",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
And it tells me 'Nooooooooooo'.