De bron van een pivot table definiëren met VBA

[reneemettrie]

Men heeft me gevraagd het maken van een pivot table in VBA uit te leggen.
Persoonlijk zie ik daar het nut niet echt van in. Zou eerder opteren voor een template met voorgedefinieerde PivotTables (zonder concrete gegevens) gebaseerd op een gebied met een gedefinieerde naam (+ Insert Table zodat die naam up to date blijft). En dan VBA om de meest recente gegevens in die tabel te plakken en pivottables te hernieuwen.

Maar ja, pivot tables moeten met VBA gemaakt worden...

Nu wil ik als bron een gebied met een gedefinieerde naam "Database" gebruiken (Insert Table gedaan om de naam actueel te houden)

Set PC = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=Range("Database"))
Set PT= PCache.CreatePivotTable _
(TableDestination:=Activesheet.Cells(1, 1), TableName:="Sales")

Maar als ik in Excel de bron bekijk, zie ik daar ip.v. "Database" de celreferenties. Dus niet erg flexibel als die database wordt aangevuld.

Hoe los ik dit op?

 

[edmoor]

Ik heb een document waarin ik dat zo doe:

            With Sheets("Data")
                LRdata = .Cells(.Rows.Count, "A").End(xlUp).Row
                SrcData = "Data!$B$1:$W$" & LRdata
            End With
            
            Sheets("DASHBOARD").PivotTables("Draaitabel1").ChangePivotCache ActiveWorkbook. _
                PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData) [COLOR="#008000"]', Version:=6[/COLOR])
            Sheets("DASHBOARD").PivotTables("Draaitabel1").PivotCache.Refresh
            Sheets("DASHBOARD").PivotTables("Draaitabel1").SaveData = True
            ActiveWorkbook.RefreshAll

 

[jkpieterse]

Werkt dit niet:

.......  SourceData:="=Database"

 

[reneemettrie]

Edmoor, dergelijke code zag ik ook al. Maar dan met je elke keer opnieuw die macro runnen. Als de PT gebaseerd is op een benoemd gebied + insert table pas die naam zich automatisch aan en enige wat nodig is, is een refresh als je rijen toevoegt.
Snap ook niet het nut van rijen tellen, waarom niet gewoon werken met CurrentRegion?

 

[reneemettrie]

Werkt dit niet:

.......  SourceData:="=Database"

Werkt prima, bedankt! Had het al eerder geprobeerd maar zonder de "="

 

[edmoor]

Ik gebruik het in een wat ouder document.
Die is nooit ge-update omdat het goed werkt.
Currentregion gaat tot aan de eerste lege regel en dat hoeft niet het hele gebied te zijn.

 

[snb]

Of zo:

sub M_snb()
  With ActiveWorkbook.PivotCaches.Create(1, [Database]).CreatePivotTable(sheet1.cells(1), "Snb")

  end with
End Sub

 

[reneemettrie]

Of zo:

sub M_snb()
  With ActiveWorkbook.PivotCaches.Create(1, [Database]).CreatePivotTable(sheet1.cells(1), "Snb")

  end with
End Sub

Dat soort code nog nooit gezien, slaan die rechte haken op een benoemd gebied?

 

[snb]

test het eens....