VBA pivot with no range

[Lesly]

Hello,

I'm just learning VBA and i got to the point that i need often pivottable's
But when you record it always take a range!

So i thought that the following will work but it doesn't

Can anyone have a quick look and see what i did wrong or forgot?

Thx a lot for your help



[VBA]
Private Sub()
Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
Sheets.Add
ActiveSheet.Name = "test"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Pivot!R1C1:R" & LR, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="test!R3C1", TableName:="Draaitabel5", DefaultVersion _
:=xlPivotTableVersion14
Sheets("test").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("Draaitabel5").PivotFields("a")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Draaitabel5").PivotFields("b")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Draaitabel5").AddDataField ActiveSheet.PivotTables( _
"Draaitabel5").PivotFields("c"), "Som van c", xlSum
With ActiveSheet.PivotTables("Draaitabel2").PivotFields("d")
.Orientation = xlPageField
.Position = 1
End With
End Sub[/VBA]

[mancubus]

hi.

taken from your code:
[VBA]SourceData:="Pivot!R1C1:R" & LR[/VBA]

it must be like this:
[VBA]SourceData:="Pivot!R1C1:R250C10"[/VBA]

so maybe
[VBA]SourceData:="Pivot!R1C1:R" & LR & "C10"[/VBA]

an article from msdn.
http://msdn.microsoft.com/en-us/libr.../hh243933.aspx

[Lesly]

So you say that i need to put also howmany columns?
But i don't know that

so i need to create for example:

Dim LC As Long
LC = ActiveSheet.UsedRange.Columns.Count


"Pivot!R1C1:R" & LR & "C" & LC

or do i understand it wrong?

[mancubus]

yes.

but defining a dynamic named range will make it easier.

from Defined Names group in Formulas tab, click Name Manager + New or directly Define Name.

assuming your database is in worksheet named "Pivot", write a name for your named range, eg, "pvtRange", in Name box, then copy below formula to RefersTo box.
[vba]=OFFSET(Pivot!$A$1,0,0,COUNTA(Pivot!$A:$A),COUNTA(Pivot!$1:$1))[/vba]

ps: dont forget to change formula argument seperator to ; from , if it is so.