Office: (Office 2016) Wie bestimmte Zellen aus mehreren Blättern kopieren

Hallo,

ich habe ein Problem, bei dem ich trotz Googlen als Laie einfach nicht weiterkomme.

Ich möchte Zahlen aus ganz bestimmten Zellen (siehe unten), die über mehrere Tabellenblätter verteilt sind in eine Zeile reinkopieren.

Zellen:
B2:Q2;Tabelle1!B2:Q2;Tabelle2!B2:Q2;Tabelle4!B2:Q2;Tabelle5!B2:Q2;Tabelle6!B2:Q2 ;Tabelle7!C2;Tabelle7!E2;Tabelle7!G2:Q2;Tabelle8!B2:Q2;Tabelle9!B2:Q2;Tabelle10! B2:N2;Tabelle10!P2:Q2;Tabelle11!B2:Q2;Tabelle12!B2:Q2;Tabelle13!B2:Q2;Tabelle14! B2:H2;Tabelle14!J2:K2;Tabelle14!M2:N2;Tabelle14!P2:Q2;Tabelle15!B2:Q2;Tabelle16! B2:Q2;Tabelle17!B2:Q2;Tabelle18!B2:Q2;Tabelle19!B2:P2;Tabelle20!B2:Q2;Tabelle21! B2:Q2;Tabelle22!B2:Q2;Tabelle23!B2:L2;Tabelle23!N2:Q2;Tabelle24!B2:Q2;Tabelle25! B2:Q2;Tabelle26!B22;Tabelle26!F2:L2;Tabelle26!N2;Tabelle26!P2:Q2;Tabelle27!B2: Q2;Tabelle28!B2:Q2;Tabelle29!B2:Q2

Um es nochmal klarzustellen: ich will das jeder Wert wieder jeweils in einer Zelle ist, nur halt in einer Zeile direkt hintereinander.

Wie muss ich da vorgehen?
Danke!

 

Siehe auch hier: https://www.ms-office-forum.net/forum/showthread.php?t=355253

Bis später,
Karin

 

Hallo,

@Karin, Danke für deine Info.

@NoraK, folgendes VBA-Makro in ein allgemeines Codemodul (erreichbar über Tastenkombination Alt+F11) kopieren:

Code:

Public Sub ZeilenZellstapel_aus_A5()
  ZeilenZellstapel Range("[COLOR="#0000CD"][B]A5[/B][/COLOR]") '<-- In A5 steht deine Zellbereichs-Formel
End Sub

Public Sub ZeilenZellstapel(QuellBezüge As String)
  Dim Zelle1 As Range
  Dim Bereich As Variant
  
  On Error GoTo Err_Stapel
  Set Zelle1 = ActiveCell
  
  For Each Bereich In Split(QuellBezüge, ";")
      With Range(Trim$(Bereich))
           .Copy
           Zelle1.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
           Set Zelle1 = Zelle1.Offset(0, .Columns.Count)
      End With
  Next Bereich
Exit_Stapel:
  Application.CutCopyMode = False
  Exit Sub
  
Err_Stapel:
  MsgBox Prompt:="Fehler " & Err.Number & " (" & Err.Description & ")" & vbNewLine & _
                 "im Bereichselement '" & Bereich & "'", _
         Title:="Fehlerhafter Bereich", _
         Buttons:=vbCritical + vbOKOnly
  Resume Exit_Stapel
  
End Sub

Dabei nimmt das Makro an, dass deine Formel in Zelle A5 des Arbeitsblattes steht, in das die Formelzellwerte kopiert werden sollen.
Falls das eine andere Zelle sein soll, musst du die blau gekennzeichnete Stelle entsprechend ändern.

Anwendung des Makros:

1. Aktive Zelle (markierte Zelle) in die Zelle positionieren, ab der die Zellbereiche der Formel aus A5 wert-kopiert werden sollen.
2. Tastenkombination Alt+F8 drücken und das Makro "ZeilenZellstapel_aus_A5" ausführen.

 

@ Karin: Der Beitrag ist auch von mir. Hab die Frage in beide Foren gepostet um meine Chance auf eine Lösung zu erhöhen.

@Anton: Zunächst einmal vielen Dank für die Antwort. Nur bekomme ich dabei folgende Fehlermeldung: "Fehler 1004 (Die Methode 'Range' für das Objekt '_Worksheet' ist fehlgeschlagen) im Bereichselement Tabelle1!B2:Q2"
Woran liegt das?

kleine Zusatzinfo: Mein eigentliches Ziel ist es von all den Daten Quartilwerte ausrechnen zu lassen. Beim Median war es kein Problem, aber Quartilwerte rechnet Excel nur aus, wenn die Zellen lückenlos nebeneinander liegen.

 

Hallo,

ist im Zellbereich B2:Q2 des Arbeitsblattes "Tabelle1" in einer Zelle etwas enthalten, dessen Wert/Inhalt nicht in eine andere Zelle kopiert werden kann/darf?

• Es kann nämlich nicht an der Bereichsangabe "Tabelle1!B2:Q2" als solcher liegen - wenn da etwas falsch wäre zB. "Tabellle1!B2:Q2" oder nicht existierte, dann würde die Meldung "... für das Objekt '_Global'..." lauten und nicht "... für das Objekt '_Worksheet'..."
• Oder ist/sind im Zielbereich, wohin die Zellwerte aus Tabelle1!B2:Q2 kopiert werden sollen, eine/mehrere schreibgeschützte Zellen enthalten?

 

Das ist meine Excel-Datei:
---

Wie du auf dem Tabellenblatt "Tabelle 0" sehen kannst hab ich die Mediane der ausgewählten Zellen gebildet. Dasselbe wollte ich wie gesagt auch mit den Quartilen machen, was aber leider nicht ging. Also hab ich ein leeres Tabellenblatt ("Gesamt") erstellt um dort all die Werte mit deinem Code ausspucken zu lassen. Dazu bin ich einfach auf dem Tabellenblatt "Gesamt" über rechten Mausklick auf "Code anzeigen" gegangen, hab deinen Code eingefügt und dann auf "Sub/UserForm ausführen (F5)" geklickt.
Dann kam die genannte Fehlermeldung.

 

Hola,

lade die Datei bitte im Forum hoch - bei dem Hoster ist sie irgendwann weg.

Gruß,
steve1da

 

Die Datei war zu groß, aber ich hab jetzt einfach die Tabellenblätter 11-29 gelöscht.

 

Hallo,

der springende Punkt am ganzen Problem war, dass du den VBA-Code nicht in ein allgemeines Codemodul eingefügt hast, sondern in das Codemodul von Arbeitsblatt "Gesamt".

1. Ich habe jetzt das Makro an seinen richtigen Platz gestellt (Module/Modul1) und habe es leicht abgeändert, sodass es jetzt die Bereichsformel aus der Zelle A30 entnimmt.
2. Im Arbeitsblatt "Gesamt" ich in Zelle A34 eine Vorgabeliste für Testzwecke gestellt und in A37 eine solche für die Echtdatei (gemäß deiner im 1. Posting mitgeteilten Bereichsformel).
3. In Zelle A30 habe ich ferner eine Formel eingefügt, die eine vollständige Bereichlisten-Formel enthält, wobei diese Formel mit der Zeilennummer aus A31 und mit der Listenart (Test/Echt) aus D31 gefüttert wird. Diese Formel enthält ganz normale Arbeitsblattfunktionen. Durch sie werden die "#"-Zeichen in den Vorgabenlisten durch die Zeilennummer aus A31 ersetzt.
   Die sich in A30 ergebende Formel muss lauter korrekte Bereichsangaben enthalten, zB. auch keine Leerzeichen an unpassenden Stellen oder nicht existierende Bereiche - sonst bricht das Makro unter Angabe der fehlerhaften Stelle ab.
4. Ab Zelle A39 habe ich dir eine Prozedere-Liste zur Bedienung des Ganzen eingefügt.
5. Der eingefügte Button unterhalb von Zeile 30 dient nur zur leichteren Handhabe (anstelle der Tastenkombination Alt+F8, …)
6. Aufgefallen ist mir an der Formel im 1. Posting Folgendes: …; Tabelle26!B22;Tabelle26!F2:L2;Tabelle26!N2;T abelle26!P2:Q2;...(rot markierte Stelle mit abweichender Zeilennummer). Ich habe das bei der Vorgabeformel in Zelle A37 auf "#" umgestellt und nicht bei "22" belassen.)

 

Lieber Anton,

ich kann gar nicht in Worte fassen, wie sehr ich dir dankbar bin für deine Mühe. Es hat alles super geklappt (und ja die 22 war Blödsinn, es ist B2: D2, hab da wohl was gelöscht aus Versehen) und du hast es sehr verständlich erklärt. Du hast mir unglaublich viel Arbeit erspart, VIELEN VIELEN DANK!!! :)

 

Hola,

wie fast immer beim Crossposting, man vergisst die anderen Threads. Dort gab es auch noch einen Vorschlag. Zumindest das es gelöst ist hättest du dort mitteilen können.

Gruß,
steve1da

 

Danke fürs Erinnern, ist erledigt :)