Draaitabel op basis van tabel - update met kleiner bereik

[Textor]

Beste leden,

Ik heb hier al heel wat hulp gekregen in deze Wondergrot van Excel-kennis en kom mij nog eens laven aan jullie bron.
Ik heb (zie bestandje in bijlage) een tabel gemaakt, op basis waarvan ik een draaitabel laat euh... draaien. Ik plak telkens een nieuwe lijst van afspraken onder de labels (veldnamen) van elke kolom. Als de nieuwe lijst echter korter is, dan zit ik met het overschot aan afspraken van de vorige lijst. Ik zou natuurlijk de tabel telkens eerst leeg kunnen maken, maar de lijsten die ik aangeleverd krijg hebben een bepaalde opmaak die het geheel nogal slordig laat ogen.
Bestaat er "iets" waardoor bij/na het plakken van de nieuwe data de onderliggende restrijen van de vorige datalijst worden weggeveegd (zonder dat dit een negatieve impact heeft op de draaitabel nadien, want ik gebruik nadien dan nog de formule OPHALEN uit die draaitabel).
Alleszins mijn dank en prettig weekend,

Bekijk bijlage Aanvullen.xlsx

 

[MarcelBeug]

Als ik het goed begrijp, is de oorzaak van het probleem dat je "tabel" geen tabel is, maar een bereik.

Ik heb er een tabel van gemaakt en de draaitabel ook op die tabel gebaseerd.

Als je nu meer gegevens in de tabel plakt, wordt die tabel automatisch uitgebreid.
Als je minder gegevens in de tabel plakt, dan moet je de resterende rijen selecteren, rechtsklikken en kiezen voor tabelrijen verwijderen.

Als dit niet de oplossing is, dan heb ik waarschijnlijk de vraagstelling niet goed begrepen.

 

[Textor]

Tabelrijen verwijderen

Beste Marcel?,

Ik snap niet goed wat ik win of verlies met het maken van een tabel of bereik (zijn beide dan niet dynamisch voor de draaitabel?), zo sterk ben ik niet in Excel.
Ik zoek inderdaad "iets" dat een automatisch antwoord biedt op :"Als je minder gegevens in de tabel plakt, dan moet je de resterende rijen selecteren, rechtsklikken en kiezen voor tabelrijen verwijderen."
Misschien is dat wat lui van mij, maar ik omdat ik twee excel tabbladen moet importeren uit een bestaand systeem en daardoor die data al wat moet euh... "digitaal masseren" tot ze voor mij goed te benutten zijn, wil ik zoveel mogelijk manuele handelingen uitsluiten.
Hopelijk ben ik nu iets duidelijker (vragen stellen is soms moeilijker dan ze te beantwoorden hihi),
Mvg,

 

[VenA]

Een lomp en onbenullig methode:

Stel je hebt maximaal 1000 rijen met data.
- Verander het bereik van Afspraken in =Tabel!$A$1:$L$1001;
- Klik in de tab 'Data2' op A1;
- Toets in het vak net boven A1 L1000 in;
- Houdt de <Shift> toets ingedrukt en druk op <Enter>;
- Druk op <Ctrl> + c;
- Ga naar de tab 'Tabel';
- Klik op A2 en vervolgens druk <Ctrl> + p.

"digitaal masseren"

Is een macro daar niet wat handiger voor? Eigenlijk voor het hele gebeuren:d

 

[Textor]

Uitleg

Beste VenA,

met dat masseren bedoel ik volgende (opgenomen) code

Sub Uitvullen_lege_cellen()
'
' Uitvullen_lege_cellen Macro
'

'
    Selection.UnMerge
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    Sheets("Wiedoetwat").Select
End Sub

en bedien dat met een knopje op het tabblad. Maar ik weet niet hoe ik dat opkuisen (verwijderen) van de overtollige regels daarin kan verwerken, vooral als ik jouw methode zou gebruiken, waarbij het selecteerwerk in het ander tabblad gebeurt. De aanpassingen die ik aan die aangeleverde bladen moet doen, betreffen vooral gemergede cellen en - zoals ik hier al gelezen heb - is dat mergen een showstopper voor macro's.
Uw methode is zeker een oplossing, maar een macro zou misschien nog meer soelaas brengen. Hm... ik ben echt lui...
Prettige avond,

 

[VenA]

Zelfs te lui om een relevant bestand te plaatsen?

Leg het proces van wat je zoal doet eens even uit.
Je krijgt nieuwe data. Waar komt dit vandaan en hoe ziet het eruit en wat doe je ermee voordat je het gaat manipuleren?

Gebruik bij voorkeur geen Select in code
Dit doet hetzelfde, is sneller, korter en beter leesbaar.

Sub VenA()
  With Sheet1.UsedRange 'kan ook cells(1).currentregion zijn
    .UnMerge
    .Columns(1).SpecialCells(4) = Now 'date lijkt mij logischer
  End With
End Sub

Blijkbaar doe je iets met een datum en ook daar zit mogelijk een oplossing. Dus graag wat meer informatie over hoe het er in echt aan toegaat.

 

[Textor]

Relevant

Dag VenA,

Ik probeer zo veel mogelijk mijn plan te trekken maar stoot telkens op mijn onkunde en zoek eerst op internet naar een oplossing en als ik het niet vind, dan kom ik hier mijn licht opsteken. Enerzijds omdat ik zelf wil begrijpen en anderzijds de formule of macro wat kan aanpassen als er een wijziging in het aanleveren van de data zou gebeuren. Ik ben jullie heel dankbaar voor alle hulp, maar wil niet van jullie profiteren door alles door jullie te laten schrijven (misschien dom van me, want uiteindelijk maak ik er in mijn eentje uiteindelijk toch een gedrocht van door al die tips en opgezochte formules bijeen te gooien). Alles wat ik hier kreeg, werkt fantastisch en maakt mijn werk beetje per beetje efficiënter. Maar dan bedenk ik telkens weer een uitbreiding, die me weer een stapje verder kan brengen.

Ik voeg een relevant bestand bij, maar heb wel alles anoniem gemaakt.
Voor elke werkdag verspreid ik een overzicht taakverdeling (zie tabblad Taakverdeling) en boor daarvoor drie bronnen aan : een personeelslijst opgedeeld in twaalf maanden (twaalf tabbladen, voorbeeld Jul gekozen, kopieer ik uit een ander workbook), een query DagData en een query MaandData (beide via een apart Excel Workbook dat automatisch aangemaakt wordt en dat zijn gegevens gaat plukken uit een server, zoiets). Mijn behoefte is geen prioriteit en dus probeer ik zelf iets te maken waarmee ik verder kan. Ik heb de uitleg samengevat op tabblad Taakverdeling. De twee opgenomen macro's heb ik niet bijgevoegd (maar één ervan stond al geplakt hierboven in een bericht). De datum van vandaag waar je over schreef, werd enkel aangemaakt omdat ik een probleem had met een blanco cel bovenaan kolom A (eerste rij), want dat gaf een foutmelding bij het kopiëren van de bovenstaande cel. De belangrijkste handelingen op die twee DagData en MaandData bladen zijn het wegwerken van de merged cells en het aanvullen van de blanco cellen met gegevens van cellen die zich erboven bevinden. Het ziet er niet sexy uit, maar het werkt ruwweg. Bij de Dagdata werk ik via een draaitabel, en vis dan de gegevens via de formule OPHALEN uit die draaitabel.

Hopelijk is het ietsje duidelijker...
Heel erg bedankt,

Bekijk bijlage Taakverdeling.zip

 

[VenA]

Ook na x keer lezen van jouw verhaal kan ik er geen tabak van maken. Er staat nergens een tabel of iets wat er ook maar op lijkt. Een draaitabel is ook niet te vinden. Mogelijk dat een andere helper er iets van begrijpt.:d

 

[roeljongman]

Textor, ik ga even terug naar je orginele vraag en voorbeeld "Aanvullen" want je laatste reactie gaat over een heel ander probleem volgens mij.

Ik ben net als Marcel Beug voorstander van het gebruik van een Excel tabel maar ik heb dan ook een "handig macrootje" waarmee je de tabel kan leegmaken en daarna (zelf) weer nieuwe data kunt plakken. Samen zorgen die tabel en die korte macro dat je telkens niet meer hoeft te doen dan tabel leegmaken knop indrukken en je nieuwe data plakken (waar die dan ook vandaan komt)

De tabel die ik heb gemaakt heet "Tabel_afspraken" dat vind je terug in de macro

kijk eens of je hiermee verder komt..
daarna kunnen we altijd nog kijken of het knippen en plakken ook te automatiseren is. (of andere vragen aanpakken)

 

[Textor]

Sorry

Dag beste VenA,

Ik had om het duidelijk te houden enkel de beginsituatie (de twee aangeleverde "OrigineelMaand" en "OrigineelDag" queries weergegeven, zie nu in bijlage onder die tabbladen) en niet de situatie na bewerking (zie nu tabbladen "DagData" en "MaandData" en "Pivotdag"). Maar blijkbaar werd ik daardoor onduidelijker. Zie nu in bijlage hoe het er uitziet. Ik voel ook nooit goed aan wanneer ik een code moet plakken als Sub of Private Sub, of bij het tabblad zelf of bij de Module. Ik heb jouw code (zonder de select) ook nog niet geplakt, misschien zie je zo eerst beter wat ik gebrouwen heb.

@Roeljongman : mooie oplossing voor mijn opkuisperikelen. Ik snap wel niet hoe jij de button aan de code in de module gehangen hebt. Ik heb die nu in het tabblad zelf geplakt, maar krijg dan een foutmelding (fout 9, subscript valt buiten bereik). Zucht, ik ben echt hopeloos...

@VenA (je eerste grijze haren zul je van mij krijgen vrees ik) : in bijlage le Beaujolais nouveau...

Alvast heel erg bedankt !

Bekijk bijlage Taakverdeling2.0.zip

 

[roeljongman]

ach, ooit was ik ook hopeloos.. dat is het nadeel van "zelflerend" excel en vba leren kennen. ALs anderen gaan helpen kan je nog wel eens het spoor bijster.

hier een link over hoe je knoppen maakt en macro code erin zet.
https://support.office.com/nl-nl/ar...werkblad-adec9584-3925-47c9-998f-def71ff6a4a3

ik denk dat het je nog wel lukt om mijn coderegel te kopiëren en plakken

die link hierboven is 1 methode, maar excel heeft altijd meerdere.
macro''s kun je op vele manieren starten hier nog een link over hoe je macro's kunt starten
https://support.office.com/nl-nl/article/Macro-s-starten-5e855fd2-02d1-45f5-90a3-50e645fe3155

ik maak veel gebruik van de methode beschreven onder Macro's starten door te klikken op een gebied in een grafisch object

 

[Textor]

Dankjewel

Dankjewel roeljongman voor je omstandige uitleg. Dat maken en aanhaken van die knop lukt nu wel (jiehaaa!), maar ik krijg toch nog steeds de foutmelding fout 9 (subscript valt buiten het bereik), ook al heb ik nu een bereik gedefinieerd zonder de tabelveldnamen erin (wat ik eerst wel gedaan had).
Prettige avond,

 

[roeljongman]

Ik denk dat je even heel goed moet kijken naar de de namen in de code.

de tabnaam in sheets() moet exact juist zijn en de tabelnaam van het listobject ook.
en niet onbelangrijk je moet van je bereik een tabel maken (Start menu > opmaken als tabel)
geen idee of je dat gedaan hebt maar voor de zekerheid: het naambereik "afspraken" dat je al had kun je niet als listobject gebruiken.

 

[Textor]

Het lukt...

Beste roeljongman,

ik heb nog eens alles nagekeken en denk dat ik gevonden heb waar ik fout ging. Tabel was gemaakt en alle namen klopten wel, maar ik vond het raar dat de cursor bij jou in het eerste veldvak (eerste gewone cel) stond, terwijl hij bij mij in het eerste naamveldvak stond. Dus zaten mijn veldnamen mee in de selectie en bij jou niet en vermoedelijk kreeg ik daarom die bereikproblemen.
Phoe, toch al iets dat opgelost is.

Ik zou dit topic als opgelost kunnen melden, maar wil toch nog eerst zien of VenA nog iets uit de magische mouwen kan/zal schudden.

Dikke duim voor je hulp en geniet van je zondag !

 

[VenA]

(je eerste grijze haren zul je van mij krijgen vrees ik)

Heb ik al en komen niet van een vraag op een forum:d

Een tabel heeft als doel dat je gelijksoortige gegevens in een 'record' zet. Dat heb ik met deze code gedaan.

Sub VenA()
  Dim j As Long, t As Long, ar
  ar = Sheets("OrigineelDag").Cells(9, 1).CurrentRegion
  ReDim ar1(UBound(ar), 6)
  For j = 1 To UBound(ar)
    If ar(j, 1) <> "Kleur" And Left(ar(j, 1), 6) <> "Aantal" Then
      If ar(j, 1) = "" Then ar1(t, 0) = ar1(t - 1, 0) Else ar1(t, 0) = ar(j, 1)
      ar1(t, 1) = ar(j, 2)
      ar1(t, 2) = ar(j, 5)
      ar1(t, 3) = ar(j, 6)
      ar1(t, 4) = ar(j, 7)
      ar1(t, 5) = ar(j, 8)
      ar1(t, 6) = ar(j, 9)
      t = t + 1
    End If
  Next j
  With Sheets("DagData").ListObjects(1)
    If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete
    .Range.Cells(2, 1).Resize(t, 7) = ar1
  End With
  ThisWorkbook.RefreshAll
End Sub

Je krijgt nu een tabel met alleen relevante gegevens en de draaitabel wordt ook bijgewerkt zonder dat je alle 'rommel' hoeft weg te filteren.

Nb. Gebruik bij voorkeur geen samengevoegde cellen.

 

[Textor]

... en het werkt nog ook !

Beste VenA,

Mijn broek valt hiervan af. Ik was al heel opgelucht met de oplossing van roeljongman voor de clean up, maar dit is het kersje op de taart. Zal me wel wat hersenbrekerij vragen om je formule ooit te doorgronden, maar ze werkt. Zelfs op zondag...
Ik weet dat die samengevoegde cellen miserie zijn, maar ik krijg dat zo aangeleverd en heb daar geen impact op (toch niet op korte termijn).
Heel hartelijk bedankt!

Ik sluit dit topic af.