Office: Mal wieder ein Makro job to do

Hallo liebe Experten,

vor einigen wochen wurde mir sehr dabei geholfen eine Kundenliste zu erstellen. Des Weitern wurde mir geholfen filtermakros zu generieren die eine Spezielle Suche inerhalb der Liste zulassen.

nun wie sich die Dinge so ergeben habe ich nun eine neue Idee für ein Arbeitsunterstützendes Marko.

Stellen wir uns im "spreadsheet 1" eine über die Listenfunktion erstellte liste vor. Sie geht von Spalte A5 bis T2000. Im spreadsheet 2 habe ich nun über Excel ein erstes Grundlayout für eine Rechnung erstellt.

Ziel:

ich stelle mir vor, dass sobald ich die Zellen einer Zeile (z.B. von A101 bis T101) markiere und ich dann im markierten zustand das Makro aktiviere, mir der Zellwert von Spalte C ( Spreadsheet 1) an die stelle G40 (Spreadsheet 2) ausgegeben wird. Insgesammt müssen von den Spalten A bis T, acht werte auf das Spreadsheet 2 Übertragen werden. Das hilft mir den Rechnungsprozess zu automatisieren. Denn es werden dann sobald ich den kunden in meiner liste habe, alle relevanten Werte auf die Rechnung übertragen.

Also ich versuche es noch einmal zusammen zu fassen. Es soll 2 spreadsheets geben. in Spreadsheet 1 ist die Liste, in spreadsheet 2 das Rechnungslayout.
Es wäre nun super dass beim Akrivieren eines Makros meine markierten Zeilen in das Spreadsheet 2 übernommen würden.
Es geht also darum dass das makro beim starten immer weiß dass der Wert der in Spalte C, E oder F ist immer einen festen platz hat. Unabhängig von der Zeilen nummer. Alle Werte aus C1:C2000 sollen immer nach G40 (Es wäre auch noch toll wenn diese Werte über eine Userform dargestellt werden könnten mit abbruchsfunktion. Ich würde dann eine Userform generieren wo die Überschriften der einzelnen Spalten zu sehen sind und darunter deren Wert. Also als kontollfenster mit Abbruchsfunktion)

Ich habe schon makro erfahrungen bedeutet ich kann das makro anpassen. Wenn also jemand eine idee hat und Dummys verwendet kann ich mir das makro leicht anpassen.

Ich bedanke mich schon einmal im vorraus für die Zeit die Aufgewendet wird. Ich bin für alle Ideen offen. Je automatisierter der Vorgang ist, desto besser :).

Danke und noch eine schöne Restwoche.

 

Hallo N123456789,

stell doch mal eine Beispieldatei zur Verfügung.
Dann wird Deine Erklärung verständlicher, und die Hilfsbereitschaft wird (erfahrungsgemäß) auch wachsen.

 

ok also ich habe mehrere Spreadsheets wie man sehen kann.

Das ist nur eine schnell schnell gebastelte Datei. Es fehlt viel aber das Prinzip sollte auch ohne wirkliche Werte machbar sein.

Wer auf das Spreadsheet "Rechnungen" geht wird ein paar Farbfelder mit Buchstaben darin sehen. Diese Buchstaben sollen für die Werte der Spalten (A bis K) im Spreadshieht "Datenbank" stehen.

Was ich nun gerne machen würde ist, Eine Zeile in der "Datenbank" zu markieren, das makro zu aktivieren, sodass mir die Werte die ich gleich Kopieren werde in einem Fenster zur kontrolle angezeigt werden, und dann nach beendigung des Makros an den Farblich markierten stellen stehen.

Also nochmals kurz. Relevante Zeile Markieren, z.B. von A4-K4 dann Makroknopf drücken, Fenster öffnet sich mit allen werten zur Kontrolle und anschließend die Daten in den Farbigen Kästchen (im Spreadsheet "Rechnungen") vorfinden sofern mit "OK" bestätigt wurde.

Ich hoffe es kommt nun etwas verständlicher heraus wie ich mir das vorstelle. Im prinzip möchte ich eine Werteübertragung nach Starrem schema bei dem nur die Werte Variabel sind. (z.B. Stundensatz, Zeit, Kosten, etc.) Somit wird eine standardisierte Rechnung geschaffen.

Vielen Dank noch einmal an jeden der sich Zeit nimmt und mühe macht.

Gruß Nico

 

Grundsätzlich ist das kein großes Ding...

Beim Start des Makros muß nur die Zeile der aktuellen Cursor-Position ermittelt werden. Dann kann aus der Zeile jeder Wert in die entsprechende Ziel-Zelle kopiert werden.

Es ist übrigens nicht notwendig, zuerst die ganze Zeile oder einen Bereich daraus zu markieren. Hauptsache, der Cursor steht in der gewünschten Zeile.

Code:

y1 = activecell.row
activesheet.cells(y1,x1).copy destination:=worksheets("zielblatt").cells(y2,x2)

wobei natürlich die Parameter für "destination" angepaßt werden müssen... Bei Deinem ersten gegebenen Beispiel wäre das

Code:

activesheet.cells(y1,3).copy destination:=worksheets("Spreadsheet 2").cells(40,7)

"(y1,3)" = Spalte C der aktuellen Zeile
"(40,7)" = G40 ("G" als 7. Buchstabe des Alphabets)
oder auch:

Code:

activesheet.range("c" & y1).copy destination:=worksheets("Spreadsheet 2").range("g40")

Gruß, Michael

 

Hallo Michael,

Danke für deine Antwort. Das geht in die Richtung wie ich es mir vorstelle jedoch ist der Vorgang noch nicht so automatisiert wie ich mir das gedacht hatte.

Wenn ich in jede einzelne Zeile klicken muss und 8 mal das makro neu aktivieren muss, dann ist der Aufwand zum kopieren der zelle quasi gleich.
Gibt es eine Möglichkeit dass ständige neu anwählen der entsprechenden Zelle zu vermeiden?

und hätte jemand eine Idee wie ich alle zu kopierenden Werte auf einmal in eine Übersicht bekomme?

Kommt excel damit klar mit mehreren Markierten Zellen etwas anzufangen? Darüber hinaus: Versteht excel mit werten zu arbeiten die markiert sind?

Viele Grüße Nico

 

Redest Du jetzt wirklich von "Zeile" oder doch eher von "Zelle"?

Zeile: Natürlich mußt Du. Das Makro kann ja nicht wissen, aus welchen Zeilen die Werte kopiert werden sollen.

Zelle: Benutze die 2. Zeile, die eigentliche Kopieren-Zeile, entsprechend oft untereinander, um die x Werte aus der aktuellen Zeile zu kopieren. Du brauchst doch jeweils nur source und destination anzupassen...

Gruß, Michael

 

tut mir leid ich meinte Zelle.

Ich habe es so verstanden, ich kann dem makro zwar sagen wert aus spalte C ins spreadsheet 2 kopieren aber wenn ich den Wert von Spalte "XY" kopieren will muss ich erst die Zelle aktiviern indem ich sie direkt anklicke.

Ich dachte mir ursprünglich dass, aobald eine Zeile markiert ist. Alle relevanten markierten Zellen auf einmal kopiert werden können.

Macht die erklärung jetzt mehr Sinn oder hast du das schon lange so, und ich verstehe es nur nicht. Ich befürchte ich bin zu dämlich dich zu verstehen.

Gruß Nico

 

Kann man grundsätzlich, ist in diesem Fall (nicht direkt zusammenhängende Zellen) aber nicht machbar.

Was wahrscheinlich Dein Gedankenfehler ist: Du gehst davon aus, daß eine Zelle erst markiert werden muß, bevor man sie kopieren kann. Das ist normalerweise ja auch richtig. Per VBA gibt es diese Beschränkung aber nicht.

VBA muß nur wissen, aus welcher Zeile die Werte genommen werden sollen. Danach kannst Du mit .copy jede beliebige Zelle kopieren und woanders wieder einfügen, ohne den Cursor vorher platzieren zu müssen.

Wenn Du dieses Vorgehen mit dem Makro-Rekorder aufzeichnest:
- auf das Quell-Blatt wechseln
- irgendeine Zeile bestimmen und Cursor dort in irgendeiner Spalte markieren
- Makro-Rekorder starten
- beliebige (andere) Zelle in der Cursor-Zeile markieren
- "copy" bzw. Strg-C
- auf das Ziel-Blatt wechseln
- Cursor in die passende Zelle setzen
- "paste" bzw. Strg-V
- zurück auf das Quell-Blatt wechseln
- Makro-Rekorder beenden
Dann siehst Du im VBA einen Haufen von xxx.select und selection.xxx Anweisungen. Das liegt eben daran, daß das Makro genau das nachbildet, was Du manuell vorher erledigt hast. Und normalerweise kann man eben nur eine Zelle kopieren, die markiert ist.

Aber eben, wie gesagt, unter VBA gibt es diese Beschränkung nicht. Da kann "Laber" das aktuelle Blatt sein und ein Makro kann trotzdem, sozusagen "total im Hintergrund", vom Blatt "Schwatz" die Zelle C5 in das Blatt "Brabbel" auf Zelle G18 kopieren.

Man kann per .copy auch einen ganzen Bereich, und nicht nur einzelne Zellen, kopieren und woanders wieder einfügen. Aber .copy kann dabei auch nicht mehr, als was man manuell auch per Strg-C machen kann. Markiere mal 3 beliebige Zellen, die nicht direkt aneinander liegen, und drücke dann Strg-C...

Gruß, Michael

 

Hallo Michael,

Danke für deine Antwort. Die trifft voll und ganz ins schwarze! Ich bin von falschen Tatsachen ausgegangen. Danke für deine Berichtigung.

Durch deinen letzten Post bin ich nun auf eine noch verwegenere Idee gekommen. Ich würde mich freuen wenn du auch hier noch einmal kritisch drüber lesen könntest.

Ich stelle mir nun vor:
(Im Beispiel spreche ich aus vereinfachung nur noch von der Beispielzeile 25)

1) Sobald der Makro knopf gedrückt wird, kommt eine Zeilenabfrage die ich manauell, in ein sich öffnendes Fenster (inputbox), eingeben muss.

2) Das makro übernimmt nun die jeweilige Koordinate 25 in das Grundmakro auf

3) Das Grundmakro, bestehend aus einigen kopiervorgängen zwischen den Spreadsheets, kann nun mit der relevanten Zeilenangabe anfangen zu kopieren. Und zwar hat ja nun Spalte "C" aus Spreadsheet 1 immer den festen Platz "G40" auf dem Spreadsheet 2.

4) Befor der Kopiervorgang beginnt, soll sich eine Messege box auftun die nochmal alle Daten, die gleich kopiert werden sollen, Darstellt und ein "OK" benötigt um fortzufahren (kontrolle). Bei "Abbrechen" soll irgendwie rauskommen End sub oder so.

5) Nach dem bestätigen soll kopiert und eingefügt werden


Um unsere kommunikation zu vereinfachen, versuche ich nun zu fragen ob es irgendwelche Variablen gibt die fehlen? Grundsätzlich brauche ich ja um eine Zelle zu bestimmen die Koordinate der Zeile und Spalte. Ich stelle mir vor das die Koordinate der Zeile mit einem Abfrage makro eingegeben wird. die Spalten A,B,C,... sind nach meiner Logik ja "fest" und unveränderlich. Das einzig dynamische in dem Makro soll die Zeilenzahl werden.

ich habe in einem makro so eine art "abfrage" schon gemacht. Die frage ist nur habe ich die Grundlogik nun besser verstanden?

Michael über eine Antwort würde ich mich sehr freuen

Viele Grüße Nico

 

Hört sich für mich soweit eigentlich ganz ok an, ja.

Verkürzt gesagt: Du möchtest das Wechseln auf das "Listen"-Blatt und Auswählen der Zeile durch eine Abfrage der Zeilennummer ersetzen. Ok...

Von der Ergonomie her würde ich es wackelig finden, weil man ja "blind" bestimmen müßte, welche Zeile man haben möchte. Aber machbar ist das sicherlich. Letztendlich ersetzt

Code:

y1 = inputbox("Zeile?")

dann eben die Zeile

Code:

y1 = activecell.row

Da dann vorher nicht auf das Listen-Blatt gewechselt wurde, werden die Werte auch nicht vom "activesheet" kopiert. Also muß auch die "copy"-Zeile angepaßt werden:

Code:

worksheets("quellblatt").cells(y1,x1).copy destination:=worksheets("zielblatt").cells(y2,x2)

Da Du ja eine Reihe von Werten kopieren willst, würde ich übrigens empfehlen, das ganze in einer for..next Schleife zu erledigen, in der dann die Quell- und Ziel-Koordinaten z.B. per select..end select gesetzt werden. Aber dazu später...

Du möchtest, daß die Werte, die ggf. kopiert werden, vorher noch in einer Sicherheits-Abfrage dargestellt werden? Hmmm... Mehrere Ansätze...

Der technisch am wenigsten aufwändifge ist, die Werte mit chr$(10), was einen Zeilenumbruch erwirkt, zusammenzuknoten und dann in einer messagebox mit ja/nein-Abfrage darzustellen.

Etwas mehr Aufwand wäre mit einem User-Formular nötig, was aber optisch vielleicht etwas "professioneller" aussehen würde.

Und ein vielleicht ganz, ganz anderer Ansatz:

Die Zeile wird nicht per inputbox abgefragt, sondern eine Zelle auf dem Titelblatt dient als Eingabefeld. Mittels indirekt() könnten die Werte aus dem Listenblatt dann daneben dargestellt werden. Ein Druck auf einen "OK" Button kopiert dann vom Listen-. auf das Übersichts-Blatt. Hat man die falsche Zeile erwischt, ändert man eben den Wert in der Eingabe-Zelle.

Das eigentliche Kopieren könnte dann aber, nach welcher Vorbereitung für "y1" auch immer, aber prinzipiell immer so ablaufen:

Code:

' y1: zeile, aus der die werte kopiert werden sollen
' w: anzahl der zu kopierenden werte
for n=1 to w
    select case n
        case 1:
            x1=3 : y2=40 : x2=7
        case 2:
            ' ...
        case else:
            messagebox "doh!"
    end select
    worksheets("quellblatt").cells(y1,x1).copy destination:=worksheets("zielblatt").cells(y2,x2)
next n

Damit kannst Du dann in einer beliebig erweiterbaren Struktur mit den "case x" Abschnitten bestimmen, aus welcher Spalte x1 der Wert in welche Koordinate y2/x2 kopiert werden soll.

Gruß, Michael

 

Hallo Michael,

Ich merke dass wir uns bei jedem mal schreiben immer mehr an das annähern wie ich mir das vorstelle.
Vielen Dank für deine investierte Zeit.

Was meinst du genau mit "wacklig" an dem Versuch eine Inputbox als Qulle zu nehmen?

Also die Makroumsetzung ist mir eigentlich egal. Ich verstehe die Syntax nur sehr begrenzt. Jedoch wäre eine fachmaännische Ausführung durchaus in meinem Interesse. Wenn dein letztes Beispiel am Sinnvollsten ist, werde ich versuchen dass anzupassen.
Ich verstehe nur eins in der Syntax nicht. Wir haben diesen Code:

Ich sehe nur dass wir in dem Kommentar auf Y1(also meine Zeile) verweisen. Wie weiß das Makro die ganze Zeit dass wir in Y1 sind?
oder ist das Makro hier nur ein Aussschnitt nachdem wir festgelegt haben wie die Zeilenzahl einzugeben ist?

Die Abfrage ist mir Jedoch sehr Wichtig da mein Vater gerne alles 3 mal gegenprüft. Also wenn wir an der Inputbox sonst festhalten könnten wäre ich sehr froh da der ganze Ablauf dann rund wird. Ich drücke das Knöpfchen gebe die relevanten sachen ein, bekomme eine übersicht und dann wird alles ausgegeben (Narrensicher).

Über eine Weitere Antwort würde ich mich sehr freuen,

Viele Grüße Nico

 

Naja, eben in dem Sinne: Man befindet sich auf Blatt "Titel" und drückt ein Knöpfchen. Dann wird man nach einer Zeilennummer gefragt und soll das jetzt beantworten, ohne die Liste der Zeilen zu sehen. Also muß man das "blind" beantworten. Sicherlich hat man vorher auf das Blatt "Liste" gewechselt und sich die passende Zeile herausgesucht. Aber wieso dann zurück auf "Titel" und dann erst den Vorgang starten? Aber ok, ich weiß... Die Geschmacksfrage und Gewohnheit ist oftmals viel stärker, als mathematisch optimierte Abläufe. Ich kenn das und ich verstehe das. ;-)

Deswegen schrieb ich ja: Egal, wie man an y1 herankommt (inputbox, userform, zelle, ...), das eigentliche Kopieren kann so und so aussehen. Bedeutet: Beim Start dieser Routine muß eben, auf welchem Weg auch immer, y1 und w bekannt sein. w ist dabei am sinnvollsten wohl eine Konstante, y1 kann z.B. durch die inputbox abgefragt werden.

Bingo! Genau das! Wobei aber eigentlich nur die eine Zeile

Code:

y1 = inputbox("Zeile?")

direkt davor fehlt...

Gruß, Michael

 

Hallo Michael,

WOW, vielen herzlichen Dank für deine Geduld mit mir dieses Problem zu lösen. Ich werde mich gleich an die Arbeit machen. Nur noch eine letzte Frage zur Syntax:

Wo muss ich Sub und End sub und Alle "end" Begriffe hinschreiben? Weil wenn die Messegebox kommt brauch ich dringend die Möglichkeit das Programm zu beenden. In der Regel kommt ja dann diese Meldung mit "Debuggen" und es öffnet sich der Makroeditor automatisch. Das wird für meinen Dad zu verwirrend. Deswegen würde ich gerne einfach nur ein End sub einfügen wenn man bei der Messegebox NICHT auf "OK" klickt, sondern auf das "X" oben rechts.

Wenn das getan ist dann hast du einen wild fremden Menschen Wunschlos glücklich gemacht :)

Viele Grüße Nico

 

OK ich habs glaub geschafft.

Das einzige Problem ist nun die MsgBox. Kannst du mir evtl. sagen wie Ich es schaffe eine MsgBox zu kreieren die Heißt "Rechnungen"

und als Text:

- Überschrift 1
- Koordinatenpaar 1 (y1,x1)
- Absatz
- Überschrift 2
- Koordinatenpaar 2 (y1,x2)
- Absatz
- Überschrift 3
- Koordinatenpaar 3 (y1,x3)

Wie du siehst möchte ich die Überschriften Konstant beibehalten und die Koordinatenpaare sollen variabel sein. Ist das so noch möglich?

Den absatz hast du mir ja shcon geschrieben & Chr(13) & aber die koordinatien bekomme ich nicht eingefügt

wäre super wenn wir das Projekt damit noch abschließen könnten :)

 

Ich versuch mal, ein komplettes Gerüst zusammenzubasteln. Hoffentlich kannst Du das ohne Änderungen, nur mit Erweiterungen, dann auch so übernehmen:

Code:

Option Explicit

Private Sub btn_Kopieren_Click()

    Const w = 3
    Const t1 = "Liste"
    Const t2 = "Übersicht"

    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim s As String
    Dim y1 As Long
    Dim x1 As Long
    Dim y2 As Long
    Dim x2 As Long
    Dim n As Long

    Set w1 = Worksheets(t1)
    Set w2 = Worksheets(t2)

    s = InputBox("Zeile?")
    If (s <> "") Then 'es wurde nicht "abbruch" oder [x] angeklickt

        y1 = Val(s)
        If (y1 > 0) Then 'es wurde ein gültiger zahlenwert eingegeben

            For n = 1 To w
                Select Case n
                    Case 1: 'C nach G40
                        x1 = 3: y2 = 40: x2 = 7
                    Case 2: 'F nach E30
                        x1 = 6: y2 = 30: x2 = 5
                    Case 3: 'I nach C20
                        x1 = 9: y2 = 20: x2 = 3
                    '...
                    Case Else: 'mehr "w" als "case"
                        x1 = 0
                        MsgBox "doh!"
                End Select
                If (x1 > 0) Then
                    w1.Cells(y1, x1).Copy Destination:=w2.Cells(y2, x2)
                End If
            Next n

            w2.Activate

          Else
            MsgBox "nur gültige zahlen eingeben!"

        End If

          Else
            MsgBox "ok, dann eben nicht..."

    End If

End Sub

Diesen Code kopierst Du in den Source des Tabellenblatts "Titel", nachdem Du einen Button mit dem Namen "btn_Kopieren" (Steuerelemente-Toolbox!) auf das Blatt gesetzt hast. Außerdem müssen die Blätter "Liste" und "Übersicht" existieren. Ggf. kannst Du dann die Blattnamen in den "const" Deklarationen anpassen.

Gruß, Michael