Excelzeile in Variable bearbeiten, kopiertes Tabellenblatt leeren

[Fritz50]

Hallo,

Es sind zwei Probleme, die mich beschäftigen. Ausgangslage ist ein Tabellenblatt mit Buttons. Bei Buttonclicks sollen Ereignisprozeduren durchlaufen werden. Diese Prozeduren sollen teilweise in andern Tabellenblättern Veränderungen vornehmen.

Problem 1: Ich möchte eine Tabellenzeile (max. 10 Spalten) in eine (Range?)Variable kopieren, dort bearbeiten und dann in einem andern Tabellenblatt speichern. Mir scheint, dass im folgenden Code die Variable auch die Tabellenzeile ist. Jedenfalls finden die Manipulationen auch in der Tabellenzeile statt.

 Dim rBuchung As Range            ' hier wird die Tabellenzeile reinkopiert

' Wenn Kontoblatt nicht vorhanden, dieses erzeugen und einfüllen.
' Zuerst wird immer eine neues Blatt erzeugt. Wenn das Blatt schon existiert sind keine Aktionen vorgesehen.
If Not WorksheetExists(strKontoName) Then

… hier wird eine Tabellenvorlage kopiert und umbenennt

… dann wird die ursprüngliche Tabelle (Buchhaltungsjournal) bearbeitet

        If iKonto = Cells(n, "D") Or iKonto = Cells(n, "E") Then
            Set rBuchung = Rows(n)                      ' Zeile in Variable kopieren
            If iKonto = Cells(n, "D") Then              ' Soll,  E,F 1x Shift links
                rBuchung.Cells(1, 4) = rBuchung.Cells(1, 5)
                rBuchung.Cells(1, 5) = rBuchung.Cells(1, 6)

            Else        ' Haben, Inhalt Zelle E löschen, KontNr. ist schon richtig, Betrag bleibt im Haben
                rBuchung.Cells(1, 5).Delete              ' Haben, nur Inhalt Zelle "E" löschen
            End If
..
… dann hier die bearbeite Range-Variable in das neue Tabellenblatt kopieren. (fehlt noch bei mir)
… die neue Tabelle hat viel weniger Zeilen als das Original, weil nur eine Auswahl bearbeitet wird.
...Jedesmal in die nächste leere Zeile schreiben.
..
End if

Hier stelle ich fest, dass auch die Zeile im Tabellenblatt verändert ist.

Problem 2: eine Übung. Blatt kopieren/umbenennen/Inhalt löschen, neues Blatt erzeugen … und alles löschen
Wie schon erwähnt sind auf einem Blatt 2 Buttons. In den zugehörigen Click-Prozeduren werden zwei andere Blätter manipuliert.
Hier ist mein Problem, dass nicht die Kopie des Tabellenblattes geleert wird (Inhalte löschen), sondern Zeilen im Blatt, das die Buttons enthält.

Kann ein neu generiertes und umbenntes Tabellenblatt unmittelbar danach auch der laufenden Prozedur heraus selektiert werden?
Die Zeile Kopie!Rows.Delete hat Fehler ‚Objekt erforderlich‘

    Const strKontoName As String = "Kopie"

    If Not WorksheetExists(strKontoName) Then

        Sheets("verschieben").Copy After:=Sheets(Sheets.Count) 'wird ans ende gestellt
        ActiveSheet.Name = "Kopie"
        ActiveCell.FormulaR1C1 = "Kopie"     ' möchte noch „Kopie“ in Zelle A1 schreiben, eigentlich nach dem Löschen
'        Cells.Select        ' Select_Methode des Range-Objekts kann nicht ausgeführt werden
'        ActiveSheet.Clear   ' Objekt unterstützt diese Eigenschaft oder Methode nicht
'        ActiveSheet.Rows(1, 25).Delete  'Anwendungs- oder objektdefinierter Fehler
        Rows.Delete          ' löscht nichts (zumindest in "Kopie"), eher in Blatt Übung, wo die Buttons sind
'        Kopie!Rows.Delete          ' Fehler. Objekt erforderlich
'        Selection.ClearContents

    Else

Freundliche Grüsse
Fritz

 

[nochEinAndreas]

Hallo Fritz,

zu Problem Nr.1:
Wenn du mit "Set" arbeitest, enthält die Variable einen Verweis auf den Range. Wenn du dann Werte in der Variablen änderst, werden die Werte auch in der Tabelle geändert. Das hast du richtig beobachtet.
Arbeite statt dessen ohne "Set":

Dim vBuchung As Variant
vBuchung = Rows(n)

Aber Achtung: Wenn du wie hier die gesamte Zeile übergibst, ist vBuchung dann ein Datenfeld mit 16384 Elementen. Du sprachst ja davon, dass du nur 10 Spalten brauchst. Dann würde ich es eher so machen:

vBuchung = Range("A" & n & ":J" & n)

Dann erhältst du eine Datenfeld mit 10 Elementen. Allerdings, wie immer, wenn man einer Variablen einen Range zuweist, ist des zweidimensional:
vBuchung(1,1) vBuchung(1,2) vBuchung(1,3) vBuchung(1,4) ...
Und jetzt kommen deine Verschiebungen; diesmal nur im Datenfeld und nicht in der Tabelle:

vbuchung(1,4) = vbuchung(1,5)
vbuchung(1,5) = vbuchung(1,6)

Dann kannst du das Datenfeld in die neue Tabelle schreiben:

Range("A" & n & ":J" & n) = vBuchung

zu Problem Nr.2:
Was ich nicht verstehe: Wenn du sowieso in der Kopie alles löschen willst, warum erstellst du statt einer Kopie nicht einfach ein neues Blatt?:

Worksheets.Add

Dann ist es doch leer.
Falls ich da was nicht verstanden habe, und du eine Kopie brauchst: Nach dem Erstellen der Kopie ist diese das ActiveSheet. Um dort alles zu löschen, sollte das hier gehen:

ActiveSheet.Cells.Clear

Gruß, Andreas

 

[Fritz50]

Hallo Andreas,

Danke, die Vorschläge haben mich weitergebracht.
Ein Problem besteht noch. Ich kann die Variable vBuchung nicht in die neue Tabelle schreiben.
Die Fehlermeldung zur Zeile strKontoName!Range("A" & nrZeile & ":H" & nrZeile) = vBuchung ist: (Fehler beim Kompilieren) Bezeichner muss eine Auflistung angeben.
Ist es weil die Clickprozedur 'Private' ist? Ohne den Tabellenname! landet die Kopie auf dem Blatt Buchungen, zu dem auch die Prozedur gehört.
Müsste die Prozedur Public in ein Modul verschoben werden?

Zum Problem 2, Kopie erstellen und Inhalte löschen anstelle Neu: Einmal interessiert mich manchmal welche Alternativen es gibt. Verschiedene Lösungen ergeben das gleiche Resultat. Mit kopieren anstatt neu kann ich auch die Formatierung übernehmen (denke ich). Oder ich möchte vielleicht die Überschriften übernehen. Dann dürfte ich allesdings erst ab Zeile zwei löschen. (wäre dann ein anderer Befehl).

Freundliche Grüsse
Fritz

 

[nochEinAndreas]

Hi Fritz,

was ist denn strKontoName? Ist das der Name des Blattes? Wenn ja musst du das anders schreiben:
Sheets("strKontoName").Range("A" & nrZeile & ":H" & nrZeile) = vBuchung

Gruß, Andreas

 

[nochEinAndreas]

Nachtrag:
Oder ist strKontoName die Variable, in der der Blattname steht? Dann geht es so, ohne Anführungszeichen:
Sheets(strKontoName).Range("A" & nrZeile & ":H" & nrZeile) = vBuchung

 

[Fritz50]

Hallo Andreas und andere Leser,
Ich benutze beide Versionen von obenstehenden Befehlszeilen. strKontoName ist eine Variable, sonst nehme ich andere Namen.
Bezüglich Zugriff auf ein anderes Tabellenblatt habe ich im Netz folgenden Hinweis gefunden.

Hi, auf eine andere Tabelle kann man mit folgender Formel zureifen =Tabelle1!B4. > dieser Ansatz gibt bei mir den Objektfehler
Daten kopieren kannst du z.B. mit diesem Code (dein Ansatz)

Sub kopieren()
        Worksheets("TabelleB").Range("C5") = Worksheets("TabelleA").Range("A1") + Worksheets("TabelleA").Range("A2")
End Sub

Wann/wie kann "Tabelle1!" ( mit ! ) eingesetzt werden?

Dann habe ich bei einer Übung ein Problem/Verhalten festgestellt. Diese Übung, eben für Blattzugriffe, umfasst zwei Tabellenblätter. Auf jedem Blatt sind zwei Buttons mit den entsprechenden "Private Sub CommandButton1/2_Click()" Prozeduren. Die Namen habe ich nicht geändert, die Caption schon.
Mit einem Button schreibe ich inkremental in eine Spalte der zugeordneten Tabelle, mit dem anderen Button schreibe ich ähnliches in das andere Tabellenblatt und umgekehrt.
Um das Verhalten einfacher zu beobachten, mache ich zwei Fenster nebeneinander mit den beiden Tabellen. Jetzt stelle ich fest, dass auf die Clicks im einen Blatt nicht reagiert wird. Wenn nur ein Fenster geöffnet ist, kann ich zwischen den Tabellen wechseln und jeder Click wird ausgeführt.

Freundliche Grüsse
Fritz

 

[nochEinAndreas]

Hallo Fritz,

du musst unterscheiden zwischen Excel und VBA. Wenn du in einer Excel-Zelle auf eine Zelle in einem anderen Blatt verweisen willst, dann geht das z.B. so:
=Tabelle1!B4
In der Zelle, in der diese Formel steht, wird also das selbe angezeigt wie in der Zelle B4 auf Blatt Tabelle1.
Wenn du in VBA auf die Zelle B4 auf Blatt Tabelle1 zugreifen willst, dann geht das so:
... = Sheets("Tabelle1").Range("B4")
In Excel-Formeln wird also als Trenner zwischen dem Blattnamen und der Zelle ein Ausrufezeichen benutzt, in VBA ein Punkt.
In der VBA-Version kannst du zusätzlich noch Variablen benutzen, die die entsprechenden Texte für Blatt- und Zellennamen enthalten. Statt der o.a. Zeile könntest du also auch schreiben:
strTabname = "Tabelle1"
strZellen = "B4"
... = Sheets(strTabname).Range(strZelle)
Hier entfallen also dann die Anführungszeichen in den Klammern, da dort dann statt der festen Texte die Variablen stehen.

Das was du von deiner Übung beschreibst, habe ich nicht ganz verstanden. Es kann nicht mit der Anzahl der Fenster zu tun haben, ob auf einen Klick reagiert wird oder nicht. Kannst du diese Übungsmappe mal hochladen?

Grüße, Andreas