Microsoft Excel

Herbers Excel/VBA-Archiv

Variable Daten aus Sheet kopieren

Betrifft: Variable Daten aus Sheet kopieren von: Draas
Geschrieben am: 27.07.2014 19:49:27

Hallo zusammen,

ich beschäftige mich derzeit ein wenig mit VBA und Markos in Excel. Nach jahrelanger "Handarbeit" möchte ich nun doch etwas mehr Automation haben. Allerdings stoß auch ich an meine Grenzen!

Ich verwalte gerne u.a. in Excel. Das Forum hat mir schon mit sehr vielen Spezialfiltern weitergeholfen, jedoch komme ich bei meinem heutigen Anliegen nicht weiter. So sieht mein Excel-Sheet in etwa aus (Beispiel):


  • (leerzeile)
    Kundendaten X
    (leerzeile)
    Y Vorname Nachname ...
    Y Vorname Nachname ...
    ...
    (leerzeile)
    Kundendaten X
    (leerzeile)
    Y Vorname Nachname ...
    Y Vorname Nachname ...
    ...
    (leerzeile)
    Kundendaten X
    (leerzeile)
    Y Vorname Nachname ...
    Y Vorname Nachname ...
    ...



  • Es sind vom Aufbau her immer die gleichen Teiltabellen-"Kundendaten X" untereinander vorhanden. Der Kopf ist am Anfang immer gleich: leerzeile-Kundendaten X-leerzeile. Genauso wie die Anfangsspalte in der Auflistung darunter (in diesem Beispiel das "Y"). Die weiteren Spalten füllen dann halt diverse Daten von Kunden aus. Die Zeilenanzahl pro Teiltabelle-"Kundendaten X" ist variabel, die Liste (Teiltabelle) ist also unterschiedlich lang. Die Gesamtzeilenanzahl des Sheets geht in die Tausende!

    Nun möchte ich gern ein Makro um beispielsweise nur die ersten 50 Kunden aus einer Teiltabelle-"Kundendaten X" (also nach "Kundendaten X" und der Leerzeile, die darauffolgenden 50 Zeilen) in ein neues Sheet kopiert haben. Dabei soll der Aufbau erhalten bleiben.

    Heißt, dass das neue Sheet genau so aussieht wie das Ursprungssheet, allerdings nicht aus einer variablen Zeilenanzahl besteht, sondern eben nur aus den ersten 50 Kunden pro Teiltabelle. Die Spaltenanzahl soll komplett übernommen werden, also immer die gesamte Zeile kopieren.

    Ich habe es schon mit Filtern oder dergleichen probiert. Auch an einen VBA-Code getüfftelt und Ideen zusammengesucht, leider alles ohne Erfolg und verworfen.

    Ich würde mich auf Hilfe freuen!

    Es grüßt Euch Benjamin

      

    Betrifft: AW: Variable Daten aus Sheet kopieren von: JoWE
    Geschrieben am: 27.07.2014 20:00:02

    Hallo Benjamin,
    ...viel Hilfe aus dem Forum erhalten...
    Da solltest Du eigentlich schon selbst auf die Idee kommen können, eine Mustertabelle zu posten.
    Kaum jemand hat Spaß daran eine Tabelle nach zu bauen.
    Gruß
    Jochen


      

    Betrifft: AW: Variable Daten aus Sheet kopieren von: Draas
    Geschrieben am: 27.07.2014 20:15:36

    Hallo JoWE,

    Dateien sind ja nicht immer angehangen, daher dachte ich die Beschreibung reicht zu. Habe einmal eine Probedatei angelegt. Ist natürlich praktischer zum probieren, da hast du recht: https://www.herber.de/bbs/user/91746.xlsm

    Info: Mindestens 50 Kunden sind immer pro Teiltabelle vorhanden!

    Danke.

    Herzlichst

    Benjamin


      

    Betrifft: AW: Variable Daten aus Sheet kopieren von: Daniel
    Geschrieben am: 27.07.2014 22:03:36

    Hi

    würde ich so lösen (Lösung ist bezogen auf deine Musterdatei)

    1. füge in der ersten Zeile nochmal eine Leerzeile ein, so dass die eigentlichen Daten in Zeile 2 beginnen.

    2. schreibe folgende Formel in die Zeile 2 der ersten Leeren Spalte (hier Spalte G).
    wenn die Formel in eine andere Spalte kommt, muss der Zellbezug auf G entsprechend angepasst werden:
    =WENN(UND($A2="";$A4="");1;G1+1)
    ziehe die Formel bis ans Datenende.

    3. Filtere mit dem Autofilter in der Hilfsspalte nach <=53 und kopiere die Daten in ein anderes Blatt oder filtere nach >53 und lösche die sichtbaren Zeilen

    4. Autofilter aufheben und Hilfsspalte löschen.



    Gruß Daniel


      

    Betrifft: AW: Als Makro von: Daniel
    Geschrieben am: 27.07.2014 22:18:52

    du wolltest ja ne Makrolösung
    daher hier die beschriebenen Schritte als Makrocode:

    Sub KopierenDieErsten50()
    Dim sp As Long
    Dim ze As Long
    With Sheets(1)
        '--- zusätzliche Leerzeile (überschrift) wird für Formel und Filter benötigt
        Rows(1).Insert
        
        '--- letzte Zeile und Spalte finden
        With .Cells.SpecialCells(xlCellTypeLastCell)
            sp = .Column + 1
            ze = .Row
        End With
        
        '--- Formeln zur Kennzeichnung der Datensätze pro Kunde
        With .Range(.Cells(2, sp), .Cells(ze, sp))
            .FormulaR1C1 = "=IF(AND(RC1="""",R[2]C1=""""),1,R[-1]C+1)"
            .Formula = .Value
        End With
        
        '--- Benötigte Daten Filtern und in neues Blatt kopieren
        .Columns(sp).AutoFilter field:=1, Criteria1:="<=53"
        .Range(.Columns(1), .Columns(sp - 1)).Copy
        Sheets.Add after:=Sheets(.Name)
        ActiveSheet.Cells(1, 1).PasteSpecial xlPasteAll
        ActiveSheet.Rows(1).Delete
        
        '--- Aufräumen
        .Columns(sp).AutoFilter
        .Columns(sp).ClearContents
    End With
    End Sub
    Gruß Daniel


      

    Betrifft: AW: Als Makro von: Draas
    Geschrieben am: 27.07.2014 23:58:58

    Hallo Daniel!

    Super Idee mit dem Filter! Klasse Expertise! Funktioniert einwandfrei, vielen Dank!

    PS: Zum Verständnis (hinter den Kulissen)-->

    $A2=""
    bedeutet in diesem Fall die gesamte Zeile A2? Versuche gerade den Prozessablauf richtig zu verstehen. Wenn es keine Mühe macht könntest mir den auch kurz beschreiben? Da fallen mir spontan sehr viele Filterungen ein die ich mit dem Wissen von jetzt und hier, weiter und vor allem 'einfacher' ausbauen könnte :-)

    Herzlichst

    Benjamin


      

    Betrifft: AW: Als Makro von: Daniel
    Geschrieben am: 28.07.2014 00:05:11

    $A2 heißt Zelle A2.
    das mit dem Dollar bedeutet nur, dass der Zellbezug auf Spalte A erhalten bleibt, wenn die Formel in eine andere Spalte kopiert wird. Der Zellbezug auf Zeile 2 wird hingeben mitverschoben.

    wenn ich mit VBA arbeite, verwende ich lieber die R1C1-Schreibweise für Zellbezüge und in der R1C1-Schreibweise ist es sinnvoll, alle Zellbezüge die nicht zwingend relativ sein müssen, absolut zu setzen.
    Gruß Daniel


      

    Betrifft: AW: Als Makro von: Draas
    Geschrieben am: 28.07.2014 00:13:42

    Ja, super! Das mit dem Zellbezug war mir bekannt. Mich wunderte der Ausdruck zwei hintereinanderfolgender Aufführungszeichen. Aber ist mir jetzt schon klar, die Zellen sind ja leer :-)

    Prima. Vielen Dank nochmal und einen schönen Feierabend.

    Gruß Benjamin


     

    Beiträge aus den Excel-Beispielen zum Thema "Variable Daten aus Sheet kopieren"