Office: [EXCEL 2003] Kopieren unter einer Voraussetzung

Hallo allerseits!

Ich überarbeite gerade eine Excel-Tabelle, in welcher Produkte, die unser Unternehmen verkauft, aufgelistet sind. Die Liste enthält sehr viele Angaben über die einzelnen Produkte. Ich will nun nur die wichtigsten Informationen daraus haben, um eine Preisliste zu erstellen; diese Preisliste soll also einzelne aus der "Hauptliste" gefilterte Angaben enthalten, und die Artikel sollen nur in der Preisliste aufgeführt werden, wenn in der Hauptliste der "Status" des Artikels auf "K" (=kurrant) gesetzt ist. Dieser Status wird in Spalte "J" eingepflegt.
Mein Wunsch ist folgender:
Wenn in Spalte "J" der Buchstabe "K" eingetragen ist, sollen aus der entsprechenden Zeile die Artikel-Nr. (aus Spalte C), das Gewicht (aus Spalte "I") sowie der Verkaufspreis (aus Spalte M) kopiert und in ein neues Register kopiert werden.

Das Ganze soll über ein Makro gesteuert und per "Knopfdruck" (Schaltfläche) gewählt werden können.


Vielen Dank im Voraus für eure Hilfe.

Grüsse
Matthias

 

Hi Matthias,

weise den folgenden Code einer Schaltfläche aus der Formular-Symbolleiste zu:

Code:

Sub Kopieren()
    Dim loZeile1 As Long
    Dim loZeile2 As Long
    loZeile2 = 2
    With Worksheets("Hauptliste")
        For loZeile1 = 2 To IIf(IsEmpty(.Cells(.Rows.Count, 3)), .Cells(.Rows.Count, 3).End(xlUp).Row, .Rows.Count)
            If .Cells(loZeile1, 11) = "K" Then
                Union(.Cells(loZeile1, 3), .Cells(loZeile1, 9), .Cells(loZeile1, 13)).Copy Worksheets("Kopie").Cells(loZeile2, 1)
                loZeile2 = loZeile2 + 1
            End If
        Next loZeile1
    End With
End Sub

Die Namen der Tabellenblätter musst du anpassen.

Bis später,
Karin

 

Hallo Karin,

Herzlichen Dank für deine Hilfe! Habe ein wenig Zeit gebraucht um zu merken, dass Spalte "J" die 10. Spalte ist und nicht die 11. wie in deinem Makro beschrieben, aber dann hat's wunderbar geklappt ;-)

Wie muss ich das Makro ändern, damit ich gleich mehrere Kopien durchführen kann (also zusätzlich aus einer 2. Artikelliste im gleichen File (nennen wir es "Hauptliste2") in ein Register "Kopie2")?

Kann man auch vorgeben, dass, bevor das Copy&Paste durchgeführt wird, sämtliche Inhalte und Formatierungen bestimmter Teile der Register "KopieX" gelöscht werden (also z.B. die Bereiche A3:C100 und E3:G100)?

Und wenn wir gerade dabei sind, das Ganze bis zur Unendlichkeit zu verkomplizieren: Kann man das Einfügen auf "nur Werte einfügen" beschränken? Habe in einem alten Makro von mir den Befehl PasteSpecial Paste:=xlPasteValues gefunden, jedoch weiss ich nicht, ob/an welcher Stelle ich das in diesem Makro hier einfügen kann.

Viele Grüsse
Matthias

 

Hi Matthias,

um aus einer zweiten Tabelle in eine weitere Kopie-Tabelle zu übertragen, muss der Code noch einmal durchlaufen werden, wobei dann die Namen der Tabellenblätter entsprechend geändert werden müssen, d.h. der Code muss 2 mal im Makro vorhanden sein - für jedes Paar Tabellenblätter.

Um Bereiche zu löschen - Inhalte und Formate - verwendet man den Befehl Clear:

Code:

Range("A3:A100).Clear

Wenn nur Werte eingefügt werden sollen, musst du den Code so abändern:

Code:

    Union(.Cells(loZeile1, 3), .Cells(loZeile1, 9), .Cells(loZeile1, 13)).Copy
    Worksheets("Kopie").Cells(loZeile2, 1).PasteSpacial Paste:=xlValues 

Oder man kann ihn ganz anders schreiben, indem man die Werte den betreffenden Zellen direkt zuweist ohne Kopieren:

Code:

    Worksheet("Kopie").Cells(loZeile2, 1) = .Cells(loZeile1, 3)
    Worksheet("Kopie").Cells(loZeile2, 2) = .Cells(loZeile1, 9)
    Worksheet("Kopie").Cells(loZeile2, 3) = .Cells(loZeile1, 13)

Bis später,
Karin

 

Danke für deine Mühen.

Beim Löschen ist noch wichtig, dass man das entsprechende Arbeitsblatt angibt, falls die Aktion nicht im selben Blatt durchgeführt wird, in der sich die Schaltfläche befindet.. Habe mir da grad mal eben die gesamte Spalte A rausgekippt im Original anstatt im Kopie-Sheet ;-)
Habe das jetzt mit folgendem Code gelöst:

Code:

Sheets("Kopie").Select
Range("A4:Q4").Clear

Betreffend das Einfügen von Werten; mein Code sieht jetzt wie folgt aus:

Code:

Union(.Cells(loZeile1, 2), .Cells(loZeile1, 22), .Cells(loZeile1, 23), .Cells(loZeile1, 24), .Cells(loZeile1, 25), .Cells(loZeile1, 26)).Copy Worksheets("Kopie").Range("A5").Cells(loZeile2, 19).PasteSpecial(Paste:=xlValues)

(Ich habe

Code:

.Range("A5")

zusätzlich eingebaut, weil ich eine Überschrift habe und die eingefügten Werte erst ab Zeile 5 erscheinen dürfen. Hat soweit funktioniert.)
Für das Einfügen von Werten musste ich deinen Vorschlag auf

Code:

.PasteSpecial(Paste:=xlValues)

abändern, er hat das sonst nicht angenommen ohne die Klammern.
Wenn ich nun aber das Makro laufen lasse, kommt folgende Fehlermeldung:
"Laufzeitfehler '1004':
Die PasteSpecial-Eigenschaft des Range-Objektes kann nicht zugeordnet werden."

Hast du eine Idee, worin der Fehler besteht?

 

Hi Matthias,

wenn kein Bezug zu einem Tabellenblatt im Code steht, wird immer das gerade aktive zur Grundlage genommen.

In VBA kann zu 99% auf Select (und Activate) verzichtet werden - das steigert die Performance des Codes. In deinem Fall reicht diese Zeile:

Code:

Worksheets("Kopie").Range("A4:Q4").Clear

Bei Verwendung von PasteSpecial muss der gesamte Zellebereich angegeben werden, es reicht nicht aus, nur die erste Zelle zu benennen - sorry, daran hatte ich nicht gedacht. Mein Code müsste also wie folgt lauten:

Code:

Sub Kopieren()
    Dim loZeile1 As Long
    Dim loZeile2 As Long
    loZeile2 = 2
    With Worksheets("Hauptliste")
        For loZeile1 = 2 To IIf(IsEmpty(.Cells(.Rows.Count, 3)), .Cells(.Rows.Count, 3).End(xlUp).Row, .Rows.Count)
            If .Cells(loZeile1, 10) = "K" Then
                Union(.Cells(loZeile1, 3), .Cells(loZeile1, 9), .Cells(loZeile1, 13)).Copy
                With Worksheets("Kopie")
                    .Range(.Cells(loZeile2, 1), .Cells(loZeile2, 3)).PasteSpecial Paste:=xlPasteValues
                End With
                loZeile2 = loZeile2 + 1
            End If
        Next loZeile1
    End With
End Sub

Die Überschrift musst du vor der Schleife kopieren, da sie nur einmalig erforderlich ist. Deine diesbezügliche Codezeile habe ich leider nicht verstanden.
Wenn die Werte erst ab Zeile 5 beginnen, dann musst du den Schleifenzähler loZeile1 zu Beginn einfach auf 5 anstelle 2 setzen.

Bis später,
Karin

 

Hi Karin,

Jetzt funktioniert das Ganze :) Und ich kann mich wiederum nur bedanken für deine super Unterstützung!

Für die Überschrift habe ich keine Codezeile implementiert, da ich die Überschriften im Blatt "Kopie" etwas anders gestaltet habe als im Original und daher keine Kopie der Überschrift vorgesehen habe. Das .Range("A5") war lediglich um sicherzustellen, dass erst ab Zeile 5 eingefügt wurde, um die Überschrift nicht zu überschreiben. Aber das kann ja auch mit dem Schleifenzähler gelöst werden, wie du das unten beschrieben hast.

Noch eine Frage: Mit

Code:

.Clear

werden ja jeweils Inhalt und Formatierung eines Bereichs gelöscht. Gibt es auch einen Befehl, durch den nur der Inhalt gelöscht, die Formatierung aber beibehalten wird?

Viele Grüsse
Matthias