Office: Makro

Hallo,

in der Anlage findet ihr ein Datei, bei der aus zwei getrennten Bereichen (Eingabebereich 1 und Eingabebereich 2) ausschließlich die gefüllten Zeilenin eine andere Tabelle (Datenbank) übertragen werden sollen. Hierzu habe ich das Makro 1 erstellt.

Die beiden Eingabebereiche müssen getrennt voneinander sein.

Bei der Ermittlung der gefüllten Zeilen arbeite ich mit lng.letzte/specical cells.

Es kann durchaus vorkommen, dass nur ein Bereich gefüllt ist. Bei einem älternen Makro habe ich die Bereiche separat überprüft. Daher ist das Makro falls ein Bereich nicht gefüllt war auf die Bretter gegangen (no Rows).

Ich wollte daher die beiden Eingaben in einem zusätzlichen Bereich zusammenführen und anschließend diesen über Inhalte einfügen sortieren lassen (Hilfstabellen). Durch die Sortierung möchte ich nun für beide Bereiche überprüfen, wo die letzte befüllte Zelle ist.

Leider erkennt das Makro1 nicht, wann die letzte Zelle gefüllt ist und fügt den kompletten Bereich ein. Mache ich die einzelnen Schritte händisch klappt das aber.

Ich hoffe das war verständlich.

Habt jemand eine Idee wie ich das Lösen kann? Wie gesagt es sind zwei getrennte Bereich und es kann durchaus vorkommen, dass ein Bereich nicht gefüllt ist und es soll alles durch ein Makro gelöst werden.

Ich bitte um Hilfe!

 

Hallo Joscha,

ich habe mir mal erlaubt, den Modul-Bereich den Realitäten anzupassen .

So, ich denke, dass ich die Lösung erwischt habe. Es sind nur die Daten, nicht die Formeln übertragen worden. Das ist hoffentlich OK.
Rufe die Sub Copy2Db() auf und passe anschließend den Code deinen Gegebenheiten an.

 

Hallo Nemo,

vielen Danke für deine Hilfe.

Ich habe das Makro ein paar mal getestet und mir ist folgendes aufgefallen: Damit das Makro wie gewünscht arbeitet, muss ich es immer aus dem Ziel-Sheet (Neue Datenbank) ausführen. Es scheint so als würden die zu kopierenden Daten immer in das gerade aktive Sheet eingefügt.

Ehrlich gesagt übefordert mich deine Lösung gerade noch etwas, da es auch sehr von meinem bisherigen Makro abweicht. Insbesondere, dass im Makro nicht die Befehle Copy/Paste auftauchen verwundert mich.

Kannst du mit bitte noch etwas über die Funktionsweise des Makros schreiben?

Vielen Dank!

Joscha

 

Ergänzung

Anbei nach mal meine aktuelle Datei, falls ich aus versehen etwas geändert haben sollte.

Eine Sache hatte ich gerade noch vergessen: im Zielsheet (Neue Datenbank) werden die Daten aus den beiden Eingabebereiche nicht angehängt, sondern überschrieben.

Kannst du dir das bitte noch einmal anschauen?

DANKE!

Joscha

 

Hallo Joscha,

es waren (wirklich) nur einige Punkte (im Code), die ich nicht gesehen hatte. Nun soll das Teil von überall aus laufen.

HTML:

   strZieltab = "NeueDatenbank"
   For i = 1 To Worksheets.Count
      If Sheets(i).Name = strZieltab Then
         ZTabVorhanden = True
         Exit For
      End If
   Next i

Hier wird geprüft, ob das Tabellenblatt mit dem Namen NeueDatenbank schon existiert. Falls ja, wird der Variablen ZTabVorhanden der Wert TRUE zugewiesen.

HTML:

   If Not ZTabVorhanden Then
      Sheets.Add After:=Sheets(Sheets.Count)
      ActiveSheet.Name = strZieltab
   End If

... und wenn nicht vorhanden, dann wird sie angelegt und mit dem Namen versehen.

HTML:

Set objSrcTab = Sheets("Tabelle2")
   Set objZielTab = Sheets(strZieltab)
   Set rngEingabe1 = objSrcTab.Range("A7:E13")
   Set rngEingabe2 = objSrcTab.Range("A18:E24")
   intSpAnz = 4   'Dort muss >0 sein

Den Variablen das Sheet bzw. den Bereich zuweisen. intSpAnz = 4 -> Spalte 4, dort steht die Anzahl drinnen.

HTML:

   With objZielTab
      'Überschriften in Zieltab schreiben
      For i = 1 To 5 'Jede Spalte
         .Cells(1, i) = objSrcTab.Cells(6, i)
      Next i
      lngDestRow = 1
   End With

Durch das With wird alles, was mit dem Punkt beginnt, so "umgewandelt": ObjZieltab.Cells(1,i) = objSrcTab.Cells(6, i). Also: In der Tabelle NeueDatenbank ist in Zeile1 Spalte(i) der Wert, der in der Tabelle Tabelle2 (SrcTab) in Zeile 6 Spalte(i) steht. Das ist ähnlich wie eine Formel in der Zieltabelle, nur werden hier Werte eingefügt. Wenn du in der Quell-Tabelle nachher etwas änderst, wird sich in der Zieltabelle nichts ändern.

Mit der Übernahme der Daten funktioniert das genau so. Ich schreibe also in die entsprechende Zelle direkt den Wert, der in der Quelltabelle steht.

So, den Rest habe ich in der koorigierten Version kommentiert.

Wozu allerdings Tabelle1 gut ist, weiss ich nicht. Und jeder Aufruf des Makros hängt die Daten der beiden Eingabebereiche in der Zieltabelle an!

 

Hallo Nemo,

vielen Dank das funktioniert jetzt sehr gut. Auch danke für die Erklärung, dass macht es viel verständlicher für mich.

Die Tabelle 1 war als meine eigentliche Zieltabelle gedacht.

Eine Frage habe ich noch: ich hatte in meiner Ausgangssituation geplant die Datenbank (Zieltabelle) in einer separaten Datei anzulegen. In meinem Ausgangssachverhalt habe ich mein Problem so übersichtlich wie möglich dargestellt. Ich dachte das Öffnen einer anderen Datei und das anschließende einkopieren werde ich schon hinbekommen.

Leider ist dem mit deiner Version nicht mehr so. In der Anlage habe ich es versucht. Die Zieldatei wird zwar geöffnet und auch ein neues Sheet angelegt. Jedoch das Übertragen der Daten klappt noch nicht.

Kannst du dir das bitte noch einmal anschauen?

Danke!

Joscha

 

Hallo Joscha,

ich bin einige Tage on the road und habe da meist kein Excel zur Hand. Ich melde mich am Wochenende wieder (bitte erinnere mich daran), dann habe ich auch Zeit und Muße ...
(Da soll aber niemanden von den anderen Helfern daran hindern, kurz mal zu testen und die Antwort zu geben ...)

 

Ich hab nur mal 'n kurzen Blick bislang reingeworfen, aber was mir in jedem Fall dazu einfällt:

Der Source hat enorm viele Fragmente aus dem Makro-Rekorder. Da müßte ganz allgemein echt mal aufgeräumt und optimiert werden.

Allgemein, sowohl zur Optimierung, als auch zur Übersichtlichkeit, insbesondere wenn nicht nur mit mehreren Blättern, sondern auch noch mit mehreren Mappen gearbeitet wird: Benutze Objekt-Variablen, die auf die Mappen / Blätter gesetzt werden und benutze diese dann bei jeder Referenz. Nur einfach

Code:

sheets("xxx").activate

Ist eine schlechte Idee in der Umgebung, weil ggf. das Blatt in der falschen Mappe angesprochen wird. Aber

Code:

thisworkbook.worksheets("xxx").activate

ist vielleicht etwas umständlich auf Dauer. Ein

Code:

dim w1 as workbook
dim s1 as worksheet
dim r1 as range

dim w2 as workbook
dim s2 as worksheet
dim r2 as range

[ ... ]

set w1 = thisworkbook
set s1 = w1.worksheets("quellblatt")
set r1 = s1.range("b2:d4")

set w2 = workbooks("externemappe.xls")
set s2 = w2.worksheets("zielblatt")
set r2 = s2.range("f6:h8")

[ ... ]

[B]w1.activate
s1.activate
r1.copy destination:=r2
[/B]
w2.activate
s2.activate

sieht da doch schon recht übersichtlich aus, oder? Und für spätere Änderungen ist das auch viel universeller, weil dann nicht quer durch den Code überall "xxx" angepaßt werden muß, sondern eben nur weiter oben die "set" Anweisung.

Etwas weiter unten in Deinem Source wird ja auch mit diesen Objekt-Variablen gearbeitet, aber eben nicht im oberen Teil, alles in allem nicht konsequent genug meiner Meinung nach. Ich persönlich bin aber auch kein großer Freund von "with" Blöcken. Meistens geht's, aber zwei ineinander verschachtelte davon machen meist höllische Probleme. Deswegen vermeide ich die, wo ich nur kann.

Das "Suchen" nach dem Blatt in der externen Mappe ist mit dieser Methode übrigens auch etwas leichter. Wenn die mit "set" gesetzte Variable "nothing" ist, dann existiert das Blatt nicht und muß eben erzeugt werden.

Code:

   strZieltab = "NeueDatenbank"
   For i = 1 To Worksheets.Count
      If Sheets(i).Name = strZieltab Then
         ZTabVorhanden = True
         Exit For
      End If
   Next i
   If Not ZTabVorhanden Then
      [...]

könnte dann werden:

Code:

   strZieltab = "NeueDatenbank"
   set s2=sheets(strzieltab)
   If (s2 is nothing) Then
      [...]

Gruß, Michael