Tabel dynamisch maken via VBA

[jempi10]

Heb nog een probleempje:

Via VBA (opgenomen macro's) probeer ik een tabel (tabblad 'Opvraging') dynamisch te maken.
Telkens ik een nieuwe opvraging doe, wordt de tabel eerst leeggemaakt.


Dit is de code :

' het werkblad leegmaken

    Sheets("Opvraging").Select
        Cells.Select
    Selection.Delete Shift:=xlUp
    Range("C8").Select

 ' bepalen dynamissche tabel voor opmaken draaitabel

Sheets("Opvraging").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
        Columns("A:I").Select
    Columns("A:I").EntireColumn.AutoFit
    Range("A1").Select
        Application.CutCopyMode = False
    [COLOR="#FF0000"]ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$3"), , xlYes).Name = _
        "Tabel3"    ' zit hier de fout :  Range("$A$1:$L$3")???[/COLOR]    
    Range("Tabel3[#All]").Select
    ActiveSheet.ListObjects("Tabel3").TableStyle = ""
    Selection.AutoFilter

Is het mogelijk? Zo ja, waar dwaal ik?
Alvast bedankt voor de hulp.

Bekijk bijlage Lege kolom draaitabel.xlsm

 

[HSV]

Is dit niet voldoende?

Sub Opvraging()
 sheets("draaitabel opvraging").pivottables(1).refreshtable
end sub

 

[jempi10]

Dag Harry

dat doe ik met deze code :

         Sheets("Draaitabel opvraging").Select
    Range("G5").Select
    ActiveSheet.PivotTables("Draaitabel1").PivotCache.Refresh
    Range("A6").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, False, True, True)
    Columns("D:D").EntireColumn.AutoFit

Mijn probleem is dat ik na elke opvraging een verschillende tabel krijg. Dus soms 100rijen, soms meer, soms minder.
De toepassing wordt ook gebruikt door een vriend, maar kent weinig of niks van draaitabellen.
Daarom dat ik het via VBA probeer.
In afwachting hiervan, het ik de brongegevens van de draaitabel ruim genomen (200 rijen).
Maar wat als ook deze niet voldoende zijn?
Bedankt om het te willen bekijken.
Mvg
Jempi

 

[HSV]

Dag Jempi,

Ik dacht dat je iedere keer een nieuwe draaitabel aan het creëren was.
Dus met een refresh is daarmee toch hetzelfde resultaat verkregen?

 

[jempi10]

Harry,

Wat u zegt klopt.
Maar er worden nieuwe gegevens ingegeven in tabblad 'Bijhouden gegevens'.
Via 'Opvraagknop ' kan een overzicht worden gemaakt van een gegeven uit Omschrijving (vb 'Blok1').
Deze gegevens worden opgeslagen in de tabel op tabblad 'Opvraging', tevens brongegevens voor de draaitabel.
Mijnvraag is eigenlijk of de brongegevens via VBA variabel kunnen.
De draaitabel is om een duidelijk overzicht te geven per jaar en per trimester.
Hopelijk leg ik het goed uit.
Mvg
Jempi

 

[HSV]

Zo te zien kopieer je de gegevens van tabblad 'bijhouden gegevens' naar blad 'opvraging'.

Kun je hiermee uit de voeten?

Sub hsv()
 Sheets("opvraging").ListObjects(1).DataBodyRange.Value = Sheets("bijhouden gegevens").ListObjects(1).DataBodyRange.Value
End Sub

 

[jempi10]

Inderdaad, alle gegevens die voldoen aan het gevraagde, worden gekopieerd naar Opvraging.
Had gemeend de brongegevens te kunnen bepalen via VBA.
Uw oplossing moet ik nu bestuderen; snap niet onmiddellijk wat er gebeurt (de eerste 2 lijnen van 'Bijhouden gegevens' worden steeds meegenomen)
Tevens heb ik de gegevens uitgebreid, u zal zien dat de brongegevens beperkt blijven tot 'Opvraging!$A$1:$L$47', ondanks dat de gegevens gaan tot lijn 76.

Bedankt en ik hou u op de hoogte.
Mvg
Jempi

Bekijk bijlage Lege kolom draaitabel.xlsm

 

[HSV]

Inderdaad, alle gegevens die voldoen aan het gevraagde, worden gekopieerd naar Opvraging.

Welk gevraagde bedoel je?

 

[jempi10]

Deze die u invult in tabblad 'Opvraagknop' in de Inputbox.

Typ 'Blok'

Sorry als dit niet duidelijk was.

 

[HSV]

Ach zo,

Test deze eens apart, en niet weer samenvoegen tussen jouw code.

Sub hsv()
persoon = InputBox("Geef naam")
Sheets("opvraging").Cells(1).CurrentRegion.ClearContents
 With Sheets("bijhouden gegevens").ListObjects(1).Range
    .AutoFilter 2, "=*" & persoon & "*"
    .SpecialCells(12).Copy Sheets("opvraging").Cells(1)
    .AutoFilter
 End With
End Sub

 

[jempi10]

Harry,

Dit werkt perfect.
Dus het kan in een paar regels....
Mag mijn VBA overboord gooien.
Hartelijk bedankt.
Nog een fijne avond.
Jempi

 

[HSV]

Graag gedaan Jempi.

Ik wist echt niet waar het overging, en moest door de in mijn ogen onoverzichtelijke code graven, waarbij ik natuurlijk stukken heb overgeslagen.
Totdat je kwam met de verheldering van de inputbox.

Veel plezier ermee.

 

[snb]

of:

Sub hsv_snb()
  Sheets("filter").Cells(1).CurrentRegion.ClearContents

  With Sheets("bijhouden gegevens").ListObjects(1).Range
    .AutoFilter 2, "=*" & InputBox("Geef naam") & "*"
    .Copy Sheets("filter").Cells(1)
    .AutoFilter
  End With
End Sub

 

[jempi10]

Ook aan snb bedankt.
Moet toegeven dat ik er een hele brok zal aan hebben, vb waarom ' Sheets" filter" ' ; 'Autofilter 2' ?

Word nu overrompeld met professionele programmatie... komt er van als je bent blijven steken in een combinatie van Basic en Macro's.

Ga nu verder zoeken of en hoe ik de tabel in Opvraging dynamisch kan maken, zodat de bronvermelding automatisch aangepast wordt.

 

[jempi10]

Heb bekomen wat ik wou.
Was van een verkeerde instelling vertrokken.
Om de brongegevens van de draaitabel, gebaseerd op Opvraging’, automatisch te laten aanpassen, dacht ik van de gegevens in Opvraging, een dynamische tabel te maken.
Mijn oplossing: ik tel het aantal rijen in de tabel Opvraging en gebruik dit om de brongegevens aan te passen.
Met deze routine:

' brongegevens draaitabel bepalen
       Sheets("Draaitabel opvraging").Select
    Range("K8").Select
    ActiveSheet.PivotTables("Draaitabel1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        [COLOR="#FF0000"]"Opvraging!R1C1:R" & a + 1 & "C12"[/COLOR] _
        , Version:=xlPivotTableVersion12)

Bedankt Edmoor (verbergen kolommen), HSV en snb die me geholpen hebben efficiënter te programmeren en allen die hebben willen meedenken.

Bekijk bijlage Lege_kolom_draaitabel.xlsm