PDA

View Full Version : VBA pivot with no range



Lesly
09-25-2012, 11:17 PM
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




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

mancubus
09-26-2012, 04:49 AM
hi.

taken from your code:
SourceData:="Pivot!R1C1:R" & LR

it must be like this:
SourceData:="Pivot!R1C1:R250C10"

so maybe
SourceData:="Pivot!R1C1:R" & LR & "C10"

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

Lesly
09-26-2012, 05:29 AM
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
09-26-2012, 08:54 AM
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.
=OFFSET(Pivot!$A$1,0,0,COUNTA(Pivot!$A:$A),COUNTA(Pivot!$1:$1))

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