Draaitabel, dynamisch bereik, draaitabel overlappen

[ce123]

Ik heb een probleem,

Ik heb in een werkblad meerdere draaitabelen staan (naast elkaar, met telkens 1 kolom ertussen) de draaitabellen halen allen van een ander werkblad dezelfde data gegevens op.

De data gegevens veranderen (data gegevens worden gewist, behalve de kolomkoppen, en opnieuw gevuld). Het werkblad heb ik gedefinieerd, een dynamisch bereik gegeven.
formule luidt =VERSCHUIVING(BladHenk!$A$1;0;0;AANTALARG(BladHenk!$AB:$AB);AANTALARG(BladHenk!$1:$1))


vervolgens wil ik dus mijn draaitabellen vernieuwen, maar dan heb ik een aantal problemen.

1) De gegevens blijven onveranderd, nadat ik op de knop gegevens vernieuwen heb gedrukt. (terwijl ik toch een dynamisch bereik, heb opgegeven.)

2) geef ik het bereik daarna weer handmatig aan, dan zegt mijn computer dat hij de draaitabel niet kan maken omdat de draaitabellen elkaar overlappen.

Mijn vraag is hoeveel kolommen moeten er tussen de verschillende draaitabellen zitten, zonder dat ik de melding overlappen krijg? (let op, ik verander niets aan de layout van de draaitabel, enkel de gegevens moeten vernieuwd worden)

mijn tweede vraag luidt. hoe zorg ik ervoor dat die gegevens automatisch vernieuwd worden (dus dat hij wel de dynamisch bereik meeneemt)

Ik hoop dat iemand mij kan helpen.

 

[gelens]

Beste CE123

Klopt het dat jouw data helemaal doorlopen tot kolom AB ?

Misschien kan jij een klein voorbeeldje bijvoegen.

 

[ce123]

Beste gelens,

Ja het klop dat mijn data tot $AB loopt (er zijn ook een paar duizend regels )
Bijgaand heb ik een klein bestand gevoegd, waarin het principe staat.

Hoop dat het lukt
bedankt voor de moeite alvast.

 

[gelens]

Beste CE123,

In de formule van het dynamische bereik gebruik jij kolom D. Deze kolom bevat echter lege cellen, waardoor de onderste regels van de data buiten het bereik vallen. De kolom bevat 25 gevulde cellen terwijl er 26 regels zijn.

=VERSCHUIVING(Blad3!$A$1;0;0;AANTALARG(Blad3!$A:$A);AANTALARG(Blad3!$1:$1))

Probeer het eens met een kolom die volledig gevuld is. In jouw voorbeeld kolom A

 

[cow18]

ik zou vooreerst het dynamisch bereik kwa aantal kolommen niet vrij laten kiezen. Als je per ongeluk nog wat data bijzet in je eerste rij, dan kan dit problemen geven, dynamisch kwa rijen, daar zie ik geen graten in, maar dan inderdaad met kolom A
Vervolgens zou ik een ruim aantal lege kolommen tussen iedere draaitabel laten en die verbergen zodat visueel de draaitabellen toch bij elkaar staan.
Dan zou ik nog een stukje VBA toevoegen, als je bv. naar het blad van je draaitabellen gaat, dat je draaitabellen reeds vanzelf vernieuwd zijn, zodat je ze niet 1 voor 1 moet vernieuwen

 

[ce123]

Hartelijk dank iedereen voor de goede tips!

Ik heb de kolommen in de formule aangepast naar A. Nu werkt het goed.
tevens heb ik meer kolommen tussen de draaitabellen gedaan. Krijg nu ook geen foutmelding meer (overlappen).

VbA heb ik ook gemaakt, maar duurt wel lang (2 a 3 minuten)voor dat hij drie draaitabellen heeft vernieuwd.

Sheets("Draaitabel Polis").Select

Dim pt As PivotTable



For Each pt In ActiveSheet.PivotTables



Select Case pt.Name

Case "Draaitabel2", "Draaitabel3", "Draaitabel4"

pt.RefreshTable

Case Else

End Select

Next pt


End Sub

Is de code die ik heb ingevoerd. Mischien heeft het er ok mee te maken dat het databestand bijna een paar duizend regels beslaat .

Of is er een snellere macro te vinden? Alle draaitabellen hebben dezelfde brongegevens.

 

[PSVSupporter]

Wat is het voordeel van zo'n bereik?

Waarom niet als brongegeven: =Blad3!$A:$E ? Alleen maar om die lege regels?
Ik hanteer dit altijd en vink dan (leeg) uit bij een van de velden.

Of doe ik dit dan niet slim?

 

[gelens]

Beste PSV supporter,

Het voordeel van een dynamisch bereik is dat het bestand niet onnodig groot en dus ook trager wordt.
En het "probleem" met lege regels komt ook niet voor.

 

[cow18]

die "select case" heeft weinig meerwaarde.
zonder een tabblad te selecteren kan je deze macro voor ieder werkblad gebruiken, zonder fout als er geen draaitabel in dat blad staat. (Soms zie je deze constructie nog met een extra loop om alle tabbladen af te lopen)

sub Vernieuw
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
End Sub

als 3 sec te lang is, dan maak je maar een knop die je activeert wanneer het nodig is, maar op die manier ben je toch zeker dat je met de laatste gegevens werkt. Daarom zou ik dit zetten in de module van je tabblad met die draaitabellen. Bovendien zou ik wat voorzichtig zijn met een tabblad te selecteren en te activeren, omdat dat vaak geen meerwaarde heeft en het vaak tijd afpakt.

Private Sub Worksheet_Activate()
  Vernieuw
End Sub

 

[Theknurt]

Weet iemand hoe ik dit dynamisch bereik kan gebruiken bij data wat staat in een extern bestand.

Ik zet mijn data in een extern bestand en laat daar de draaitabellen naar kijken en werken.
Maar ik zou ook graag een draaittabel gebruiken waarbij de data (datum's) gegroepeerd dienen kunnen te worden.
En bij verschillende data's selecteer je 'lege cellen' zodat de groepeer functie niet werkt.

Resumé, kan ik draaitabel data dynamisch bereiken in externe data.xls bestanden ?