Office: (Office 2010) Excel Makro unabhängig von Tabellengröße

Helfe beim Thema Excel Makro unabhängig von Tabellengröße in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo Ihr Lieben, letzte Woche wusste ich noch garnicht was Makros überhaupt sind, deswegen entschuldigt meine blöden Fragen ;) Und zwar folgendes:... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von Lindaa, 10. April 2015.

  1. Lindaa Erfahrener User

    Excel Makro unabhängig von Tabellengröße


    Hallo Ihr Lieben,

    letzte Woche wusste ich noch garnicht was Makros überhaupt sind, deswegen entschuldigt meine blöden Fragen ;)

    Und zwar folgendes: Ich möchte dass mein Makro die Werte von der Tabelle 1 in Tabelle 2 kopiert, und zwar unabhängig von der Tabellengröße.

    Ich habe bisher, zu Testzwecken, folgendes Makro, welches sich auf konkrete Zellen bezieht, entworfen:

    Sub Tabelleschreiben()

    Range("A1").Value = "Projekt"
    Range("B1").Value = "Planumsatz"
    Range("C1").Value = "Istumsatz"
    Range("D1").Value = "Umsatzabweichung"
    Range("E1").Value = "Marge"

    Range("A1:E1").Font.Bold = True

    With ActiveSheet.UsedRange
    .Columns.AutoFit
    .Rows.AutoFit
    End With

    Sheets("Tabelle1").Select
    Range("C2:C13").Select
    Selection.Copy
    Sheets("Tabelle2").Select
    Range("A2:A13").Select
    Sheets("Tabelle2").Paste

    With Sheets("Tabelle2")
    Columns("A:A").ColumnWidth = 20.57
    End With

    Sheets("Tabelle1").Select
    Range("M2:M13").Select
    Selection.Copy
    Sheets("Tabelle2").Select
    Range("B2:B13").Select
    Sheets("Tabelle2").Paste

    Sheets("Tabelle1").Select
    Range("N2:N13").Select
    Selection.Copy
    Sheets("Tabelle2").Select
    Range("C2:C13").Select
    Sheets("Tabelle2").Paste

    Sheets("Tabelle1").Select
    Range("O2:O13").Select
    Selection.Copy
    Sheets("Tabelle2").Select
    Range("D2:D13").Select
    Sheets("Tabelle2").Paste

    End Sub


    Ich möchte dass mein Makro automatisch alle Werte ausliest bis eine leere Zelle kommt und nicht wie bei mir nur von Zeile 2 bis 13.


    Vielen Dank schonmal im Vorraus!
     
    Zuletzt bearbeitet: 10. April 2015
    Lindaa, 10. April 2015
    #1
  2. Exl121150 Erfahrener User
    Hallo,

    1) darf ich eine Bemerkung zu deinem geposteten Makro machen: Es enthält folgenden Makroabschnitt:
    Code:
    With Sheets("Tabelle2")
     Columns("A:A").ColumnWidth = 20.57
    End With
    und vermutlich möchtest du damit im Arbeitsblatt 'Tabelle2' die Spalte A auf eine Breite von 20.57 einstellen. Das kann möglicherweise so funktionieren, muss aber nicht. So wie dieser Abschnitt programmiert ist, stellt er im AKTIVEN Arbeitsblatt die Spalte A auf eine Breite von 20.57 ein.
    Um diese Breiteneinstellung stets mit dem Arbeitsblatt 'Tabelle2' zu erreichen, muss das Makro lauten:
    Code:
    With Sheets("Tabelle2")
     .Columns("A:A").ColumnWidth = 20.57
    End With
    Der Unterschied besteht NUR in dem Punkt am Anfang der 2.Makrozeile !! Dieser Punkt (ohne einen Objektinstanzbezeichner davor) bedeutet, dass die Objektinstanz aus der WITH-Zeile zu nehmen ist, also das Objekt 'Sheets("Tabelle2")' ist - auf das dann '.Columns(...)' angewendet wird.
    Ohne diesen Punkt müsste das Makro eigentlich einen Fehler erzeugen, tut es aber nicht, weil der Compiler per Voreininstellung 'ActiveSheet.' vor 'Columns(...)' einfügt.
    Ähnlich ist es auch mit den 'Range(...)'-Angaben, vor denen kein Objekt getrennt mit Punkt steht - auch hier fügt der Compiler automatisch ein 'ActiveSheet.' ein.

    Ich habe dein Makro nochmals in kompakterer Schreibweise angefügt:

    Code:
    Sub TabelleSchreiben2()
     Dim Ws1 As Worksheet
     
     With ActiveSheet
       .Range("A1").Value = "Projekt"
       .Range("B1").Value = "Planumsatz"
       .Range("C1").Value = "Istumsatz"
       .Range("D1").Value = "Umsatzabweichung"
       .Range("E1").Value = "Marge"
      
       .Range("A1:E1").Font.Bold = True
      
       With .UsedRange
         .Columns.AutoFit
         .Rows.AutoFit
       End With
     End With
    
     Set Ws1 = Worksheets("Tabelle1")
     With Worksheets("Tabelle2")
       Ws1.Range("C2:C13").Copy Destination:=.Range("A2")
       .Columns("A:A").ColumnWidth = 20.57
       Ws1.Range("M2:O13").Copy Destination:=.Range("B2")
     End With
    End Sub
    2) Da mir dieser dein Wunsch nicht ganz klar wurde, formuliere ich ihn nochmals (hoffentlich ist es das, was du möchtest):
    Pro Spalte im Arbeitsblatt 'Tabelle1' sollen nur solange die Werte, beginnend bei einer Startzelle, in eine Spalte von Arbeitsblatt 'Tabelle2' übertragen werden, bis in der Quellspalte die 1. leere Zelle auftaucht.

    Code:
    Sub TabelleSchreiben2_variabel()
      Dim Ws1 As Worksheet
      With ActiveSheet
       .Range("A1").Value = "Projekt"
       .Range("B1").Value = "Planumsatz"
       .Range("C1").Value = "Istumsatz"
       .Range("D1").Value = "Umsatzabweichung"
       .Range("E1").Value = "Marge"
      
       .Range("A1:E1").Font.Bold = True
      
        With .UsedRange
          .Columns.AutoFit
          .Rows.AutoFit
        End With
      End With
       
      Set Ws1 = Worksheets("Tabelle1")
      With Worksheets("Tabelle2")
         BereichVariabel(Ws1.Range("C2")).Copy Destination:=.Range("A2")
         .Columns("A:A").ColumnWidth = 20.57
         BereichVariabel(Ws1.Range("M2")).Copy Destination:=.Range("B2")
         BereichVariabel(Ws1.Range("N2")).Copy Destination:=.Range("C2")
         BereichVariabel(Ws1.Range("O2")).Copy Destination:=.Range("D2")
      End With
    
    End Sub
    '
    'Die Funktion 'BereichVariabel(Zelle)' ermittelt genau den variablen Bereich je Spalte, beginnend bei einer vorgegebenen Zelle:
    'Das Resultat dieser Funktion ist genau dieser Bereich.
    Function BereichVariabel(Zelle As Range) As Range
      Dim Zl As Long
      Zl = 0
      Do
        Zl = Zl + 1
      Loop Until IsEmpty(Zelle.Offset(Zl))
      Set BereichVariabel = Zelle.Resize(Zl)
    End Function
    
     
    Zuletzt bearbeitet: 10. April 2015
    Exl121150, 10. April 2015
    #2
  3. Lindaa Erfahrener User
    Vielen vielen Dank für deine schnelle und hilfreiche Antwort.
    Der Fehler ist wahrscheinlich dadurch entstanden dass der Codes teilweise aus aufgezeichneten Makros besteht, weil ich eben nicht viel von Programmieren verstehe :/ Danke für's Fehlerbeheben!
    Und ja... du hattest mich richtig verstanden ;)

    Ich hab noch eine Frage: Ist es möglich dass ein Makro nicht bestimmte Spalten (Spalte A, B, C...) ausliest, sondern dass man nach TabellenÜberschriften sucht?

    Also zum Beispiel: Ich möchte nicht festlegen dass der Umsatz im TabellenBlatt 1 immer in Spalte C liegt, toll wäre es wenn das Makro die erste Zeile nach dem Begriff "Planumsatz" durchsucht und dann die darunter liegenden Werte in der Spalte kopiert.
    Ist sowas möglich?
     
    Zuletzt bearbeitet: 11. April 2015
    Lindaa, 11. April 2015
    #3
  4. Beverly
    Beverly Erfahrener User

    Excel Makro unabhängig von Tabellengröße

    Hi,

    am einfachsten geht es so:

    Code:
    Sub Tabelleschreiben()
        Dim wksZiel As Worksheet    ' Variable für die Zieltabelle
        Dim lngLetzte As Long       ' Variable für letzte belegte Zeile
        Dim rngSpalte As Range      ' Variable für Spaltensuche
        Dim arrSpalten              ' Array-Variable für Spaltenüberschriften
        Dim bytZaehler As Byte      ' Schleifenzähler
        ' Array mit gewünschten Spaltenüberschriften füllen
        arrSpalten = Array("Projekt", "Planumsatz", "Istumsatz", "Umsatzabweichung", "Marge")
        ' Zieltabelle auf Variable schreiben
        Set wksZiel = Worksheets("Tabelle2")
        ' letzte belegte Zeile in aktiver Tabelle
        lngLetzte = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row
        ' Schleife über die Array-Inhalte = gewünschten Spaltenüberschriften
        For bytZaehler = 0 To 4
            ' in Zeile 1 nach Spaltenüberschrift suchen, komplette Übereinstimmung
            Set rngSpalte = Rows(1).Find(arrSpalten(bytZaehler), lookat:=xlWhole)
            ' Zeile 1 bis letzte belegte Zeile der gefundenen Spalte kopieren
            Range(Cells(1, rngSpalte.Column), Cells(lngLetzte, rngSpalte.Column)).Copy _
                wksZiel.Cells(1, bytZaehler + 1)
        Next bytZaehler
        ' Zieltabelle formatieren
        With wksZiel
            .Range("A1:E1").Font.Bold = True
            With .UsedRange
                .Columns.AutoFit
                .Rows.AutoFit
                .Columns("A:A").ColumnWidth = 20.57
            End With
        End With
        ' Variable leeren
        Set rngSpalte = Nothing
    End Sub
    

    Man kann also die letzte belegte Zeile direkt ermitteln, ohne dass man in einer Schleife jedesmal über alle Zeilen laufen muss - das beschleunigt die Performance um ein wesentliches. Außerdem birgt die Schleifen-Methode das Risiko, dass die Schleife nicht bis zur tatsächlich letzten belegten Zeile läuft sondern bereits vorher beendet wird - falls nicht alle Zellen gefüllt sind, denn die Abbruchbedingung ist ja eine leere Zelle.

    Der Code muss ausgeführt werden, wenn die Ausgangstabelle (bei dir Tabelle1) aktiv ist. Kann man bei Bedarf selbstverständlich auch so anpassen, dass er von einem beliebigen aktiven Tabellenblatt ausgeführt werden kann.

    Die Zeile .Range("A1:E1").Font.Bold = True kannst du weglassen, falls in der Ausgangstabelle die Überschriften bereits fett formatiert sind, da ja immer ab Zeile 1 kopiert wird.

    Bis später,
    Karin
     
    Beverly, 12. April 2015
    #4
  5. Exl121150 Erfahrener User
    Hallo @Karin,

    dazu möchte ich anmerken, dass mir dies durchaus bewusst war - aber da @Lindaa keine präziseren Mitteilungen über das Datenmodell machte, blieb mir nichts übrig, als den formulierten Wunsch in ein Makro zu gießen. Es könnte ja schließlich auch der Fall vorliegen, dass nach der 1. Leerzelle in der Spalte eventuell noch weitere Zellen mit Dateninhalt folgen (wie du richtig bemerktest), die jedoch nicht mehr kopiert werden sollen.

    Ich hoffe, dass damit @Lindaa die vorliegende Problematik klar wird und er/sie sich für die Makrovariante entscheiden kann, die das angemessene(re) Verfahren bei seinem/ihrem Datenbestand im Arbeitsblatt darstellt.
     
    Exl121150, 12. April 2015
    #5
  6. Beverly
    Beverly Erfahrener User
    Hi Anton,

    wenn man von dem (nicht ganz eindeutig) formulierten Wunsch ausgeht, dann wäre m.E. doch eher so etwas wie Cells(1, 1).End(xlDown).Row + 1 angebracht - aber eine Schleife??

    Bis später,
    Karin
     
    Beverly, 12. April 2015
    #6
  7. Lindaa Erfahrener User
    Vielen lieben Dank für die schnellen Antworten!

    Bei dem Code von Karin kommt bei mir allerdings der folgende Fehler:"Objektvariable oder With-Blockvariable nicht festgelegt" und makiert dann den folgenden Bereich:

    Range(Cells(1, rngSpalte.Column), Cells(lngLetzte, rngSpalte.Column)).Copy _
    wksZiel.Cells(1, bytZaehler + 1)

    Weiß leider nicht woran das liegt.
     
    Lindaa, 14. April 2015
    #7
  8. Beverly
    Beverly Erfahrener User

    Excel Makro unabhängig von Tabellengröße

    Hi,

    der Fehler tritt dann auf, wenn der Suchbegriff nicht gefunden wird. Prüfe doch mal, ob alle Begriffe aus dem Array("Projekt", "Planumsatz", "Istumsatz", "Umsatzabweichung", "Marge") auch tatsächlich so als Spaltenüberschriften vorhanden sind.

    Bis später,
    Karin
     
    Beverly, 14. April 2015
    #8
  9. Lindaa Erfahrener User
    Achso.

    Aber dazu muss ich immer auf dem Tabellenblatt 1 sein damit das funktioniert?
     
    Lindaa, 14. April 2015
    #9
  10. Beverly
    Beverly Erfahrener User
    Ja, das stand auch so in meinem Beitrag - wer lesen kann ist klar im Vorteil ;).
    Ändere den Codeteil für das Kopieren wie folgt, dann spielt es keine Rolle, in welchem Tabellenblatt man sich bei Codeausführung befindet:

    Code:
        [COLOR=#ff0000]With Worksheets("Tabelle1")[/COLOR]
            ' letzte belegte Zeile in aktiver Tabelle
            lngLetzte = [B][COLOR=#ff0000].[/COLOR][/B]Cells.Find(What:="*", SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
            ' Schleife über die Array-Inhalte = gewünschten Spaltenüberschriften
            For bytZaehler = 0 To 4
                ' in Zeile 1 nach Spaltenüberschrift suchen, komplette Übereinstimmung
                Set rngSpalte = [B][COLOR=#ff0000].[/COLOR][/B]Rows(1).Find(arrSpalten(bytZaehler), lookat:=xlWhole)
                ' Zeile 1 bis letzte belegte Zeile der gefundenen Spalte kopieren
                [B][COLOR=#ff0000].[/COLOR][/B]Range([B][COLOR=#ff0000].[/COLOR][/B]Cells(1, rngSpalte.Column), [B][COLOR=#ff0000].[/COLOR][/B]Cells(lngLetzte, rngSpalte.Column)).Copy _
                    wksZiel.Cells(1, bytZaehler + 1)
            Next bytZaehler
        [COLOR=#ff0000]End With[/COLOR]
    

    Beachte den Punkt (.) vor Rows, Range und Cells !

    Bis später,
    Karin
     
    Beverly, 14. April 2015
    #10
  11. Lindaa Erfahrener User
    Oh vielen Dank! Und entschuldige für die Frage!

    Eine letzte Frage hab ich noch ;) :

    In Tabelle 1 (Tabellenblatt 1) steht welcher Mitarbeiter wie viel gearbeitet hat
    Max Meier 5 Stunden
    Lea Meier 4 Stunden
    Max Meier 3 Stunden
    Max Meier 2 Stunden
    Frank Meier 6 Stunden
    etc.

    In Tabelle 2 (Tabellenblatt 2) steht welcher Mitarbeiter wie viel kostet
    Max Meier 20 Euro
    Lea Meier 15 Euro
    Frank Meier 30 Euro

    beide Tabellen sind unterschiedlich groß (die erste ist viel länger weil da jede einzelne Kontierung aufgelistet wird)

    Nun will ich, auf Tabellenblatt 3, diese Daten nebeneinander stellen und zwar ungefähr so:
    Max Meier 5 Stunden 20 Euro
    Lea Meier 4 Stunden 15 Euro
    Max Meier 3 Stunden 20 Euro
    Max Meier 2 Stunden 20 Euro

    Wie könnte man das am besten mit einem Makro umsetzten?
     
  12. Beverly
    Beverly Erfahrener User
    Das geht auch ohne VBA - benutze einfach die Funktion SVERWEIS()

    Bis später,
    Karin
     
    Beverly, 14. April 2015
    #12
  13. Lindaa Erfahrener User

    Excel Makro unabhängig von Tabellengröße

    Ja so hab ich es auch bisher, ich will es aber nicht jedes mal aufs neue eintippen.
    Es handelt sich bei Tabelle 1 um ungefähr 2500 Datensätze die sich auch immer wieder ändern.
    Deswegen will ich festlegen dass auf Tabellenblatt 3, in Spalte F immer ein S-Verweis steht
     
  14. Lindaa Erfahrener User
    Hab jetzt einfach das Makro aufgezeichnet. müsste eigentlich ziemlich fehlerunanfällig sein, oder?

    Müsste nur wieder unabhängig der Tabellengröße angepasst werden.

    Sub SVerweis()

    Columns("F:F").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP('Clarity-Auszug'!C[9],'MMS-Stundensätze'!C[-3]:C[2],5,0)"
    Range("F1").Select
    Selection.AutoFill Destination:=Range("F1:F2502"), Type:=xlFillDefault
    Range("F1:F2502").Select
    End Sub
     
    Zuletzt bearbeitet: 14. April 2015
  15. Lindaa Erfahrener User
    Ich bin langsam am verzweifeln.

    Ich hab jetut genau den selben Code auf eine Andere Excel-Mappe anwenden wollen, jetzt kommt immer die Fehlermeldung: "Index außerhalb des gültigen Bereichs" Excel Makro unabhängig von Tabellengröße :(

    Folgendes Makro:

    Sub Projekte_finden()

    Dim wksZiel As Worksheet ' Variable für die Zieltabelle
    Dim lngLetzte As Long ' Variable für letzte belegte Zeile
    Dim rngSpalte As Range ' Variable für Spaltensuche
    Dim arrSpalten ' Array-Variable für Spaltenüberschriften
    Dim bytZaehler As Byte ' Schleifenzähler

    ' Array mit gewünschten Spaltenüberschriften füllen
    arrSpalten = Array("Projektname", "Projekt-ID")
    ' Zieltabelle auf Variable schreiben
    Set wksZiel = Worksheets("Projekte")
    ' letzte belegte Zeile in aktiver Tabelle
    With Worksheets("Clarity-Auszug")
    ' letzte belegte Zeile in aktiver Tabelle
    lngLetzte = .Cells.Find(What:="*", SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    ' Schleife über die Array-Inhalte = gewünschten Spaltenüberschriften
    For bytZaehler = 0 To 20
    ' in Zeile 1 nach Spaltenüberschrift suchen, komplette Übereinstimmung
    Set rngSpalte = .Rows(1).Find(arrSpalten(bytZaehler), lookat:=xlWhole)
    ' Zeile 1 bis letzte belegte Zeile der gefundenen Spalte kopieren
    .Range(.Cells(1, rngSpalte.Column), .Cells(lngLetzte, rngSpalte.Column)).Copy _
    wksZiel.Cells(1, bytZaehler + 1)
    Next bytZaehler
    End With

    ' Variable leeren
    Set rngSpalte = Nothing


    Sheets("Projekte").Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes

    End Sub


    Die Zeile die bei der Fehlermeldung angezeigt wird ist diese:

    Set rngSpalte = .Rows(1).Find(arrSpalten(bytZaehler), lookat:=xlWhole)


    Vielen Dank schonmal!
     
Thema:

Excel Makro unabhängig von Tabellengröße

Die Seite wird geladen...
  1. Excel Makro unabhängig von Tabellengröße - Similar Threads - Excel Makro unabhängig

  2. Speichern mit dem Titel der Zelle A2

    in Microsoft Excel Hilfe
    Speichern mit dem Titel der Zelle A2: Moin moin, Ich habe per Makro einen Arbeitsablauf aufgezeichnet der soweit auch funktioniert. Dieser Endet jedoch im "Speichern Unter" Fenster, welches durch das Klicken von "Drucken als PDF"...
  3. Kontrollkästchen (mehrere) Makro kopieren, Problem: Zellen nicht gegenseitig überschreiben

    in Microsoft Excel Hilfe
    Kontrollkästchen (mehrere) Makro kopieren, Problem: Zellen nicht gegenseitig überschreiben: Hallo zusammen, ich arbeite gerade an einer Vereinfachung für Bestellungen. Ich habe es hingekriegt, dass Kontrollkästchen an zu wählen sind und dann bestimmte Informationen kopiert werden. Jetzt...
  4. Makro Befehl läuft auf Mac nicht

    in Microsoft Excel Hilfe
    Makro Befehl läuft auf Mac nicht: Sheets("Ausschreibungsdeckblatt - " & CStr(id)).Select ActiveWorkbook.SaveAs FileName:=sPath & Application.PathSeparator & sSupplierPrefix & CStr(id) & " " & sFirma & " WS " &...
  5. Komplexes Makro ohne Ahnung :-/

    in Microsoft Excel Hilfe
    Komplexes Makro ohne Ahnung :-/: Hallo Ihr Lieben, ich brauche ganz dringend Hilfe. Ich bin zwar mit Formeln in Excel ganz gut aufgestellt, aber mit Makros leider nicht. Ich muss für meine Eltern und mich viele Versicherungen und...
  6. Excel Datei mit Passwort versehen obwohl Makro

    in Microsoft Excel Hilfe
    Excel Datei mit Passwort versehen obwohl Makro: Hallo, ich muss meine Excel Datei mit einem Passwort versehen oder den Zugriff für Personen beschränken aber ich bekomme eine Meldung dass ich erst alle Makros entfernen muss. Gibt es dafür eine...
  7. VBA Excel in mehrere teilen nach Kostenstelle

    in Microsoft Excel Hilfe
    VBA Excel in mehrere teilen nach Kostenstelle: Hallo liebe Office Community, ich bin mit VBA nicht so bewandert und habe folgenden "Auftrag"... Eine Excel Datei aus SAP soll aufgeteilt werden nach Kostenstelle. Sprich für jede Kostenstelle...
  8. Audiodateien anhand einer Excel sortieren

    in Microsoft Excel Hilfe
    Audiodateien anhand einer Excel sortieren: Hallo zusammen, Ich stehe momentan vor folgendem Problem. Ich muss aus einer Audiobibliothek mit über 5000 Titeln jeweils 200 und 500 heraussuchen und am besten in einen separaten Ordner packen....
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden