Om hat Hunderte von Arbeitsblättern mit unterschiedlichen Namen in derselben Arbeitsmappe. Die Struktur jedes Arbeitsblatts ist im Wesentlichen dieselbe. Er möchte einen festen Bereich (A146: O146) von jedem Arbeitsblatt in ein neues Arbeitsblatt kopieren, eine Zeile nach der anderen.

Es gibt einige Möglichkeiten, wie Sie dies tun können. Für mein Geld ist ein einfaches Makro die beste Wahl. Bevor Sie sich jedoch mit dem makrobasierten Ansatz befassen, können Sie dies mithilfe von Formeln tun. Eine ziemlich einzigartige Möglichkeit besteht darin, eine Formel im Namensmanager zu definieren. Führen Sie die folgenden Schritte aus, um zu beginnen:

  1. Zeigen Sie die Registerkarte Formeln des Menübands an.

  2. Klicken Sie in der Gruppe Definierte Namen auf das Namensmanager-Tool. Excel zeigt das Dialogfeld Name Manager an.

  3. Klicken Sie auf die Schaltfläche Neu. Excel zeigt das Dialogfeld Neuer Name an. (Siehe Abbildung 1.)

  4. Geben Sie im Feld Name den Namen ListSheets ein (beachten Sie, dass dies ein einzelnes Wort ohne Leerzeichen ist).

  5. Geben Sie im Feld Verweise auf die folgende Formel ein:

  6. Klicken Sie auf die Schaltfläche OK, um die Erstellung des benannten Bereichs abzuschließen. Der neue Bereich sollte im Dialogfeld Name Manager angezeigt werden.

  7. Klicken Sie auf die Schaltfläche Schließen, um das Dialogfeld Namensmanager zu schließen.

Mit der auf diese Weise definierten Formel können Sie dann zu dem Arbeitsblatt gehen, auf dem Sie alle diese Bereiche zusammenführen möchten. (Angenommen, der Name dieses Arbeitsblatts lautet „Zusammenfassung“.) Ich empfehle dringend, sicherzustellen, dass dieses Arbeitsblatt das allerletzte in Ihrer Arbeitsmappe ist. Fügen Sie im Arbeitsblatt „Zusammenfassung“ die folgende Formel in Spalte A einer beliebigen Zeile ein:

=INDIRECT(INDEX(ListSheets,ROWS($A$1:$A1))&"!"&CELL("address",A$146))

Kopieren Sie die Formel nach unten, es sind jedoch viele Zeilen erforderlich, um alle Arbeitsblätter darzustellen. Mit anderen Worten, wenn Sie 25 Arbeitsblätter haben (ohne das Arbeitsblatt Zusammenfassung), kopieren Sie die Formel in 24 Zeilen. Wenn Sie das Original zählen, sollte die Formel jetzt in insgesamt 25 Zeilen angezeigt werden.

Eine Randnotiz hier: Die ListSheets-Formel, die Sie im Namensmanager definiert haben, gibt ein Array von Arbeitsblattnamen zurück. Die ROWS-Funktion wird verwendet, um zu bestimmen, welches Element dieses Arrays über die INDEX-Funktion zurückgegeben wird. Wenn Ihr Zusammenfassungsarbeitsblatt nicht das letzte in Ihrer Arbeitsmappe ist, kann es problemlos von ListSheets zurückgegeben werden, und Sie werden am Ende Werte daraus abrufen. Dies ist zweifellos etwas, das Sie nicht tun möchten, weshalb ich vorgeschlagen habe, sicherzustellen, dass Zusammenfassung das letzte Arbeitsblatt in der Arbeitsmappe ist.

Kopieren Sie nun einfach die Formeln aus Spalte A nach rechts, sodass Sie sie vollständig in Spalte O haben. Das Ergebnis ist, dass Sie die Werte von A146: O146 in den Zellen haben, die die Formeln enthalten.

Vorhin habe ich gesagt, dass ich denke, dass ein makrobasierter Ansatz die beste Wahl ist.

Hier ist ein kurzes kleines Makro, das zeigt, warum dies der Fall ist.

Sub CopyRange()

Dim w As Worksheet     Dim sRange As String     Dim lRow As Long

sNewName = "Summary" 'Name for summary worksheet     sRange = "A146:O146" 'Range to copy from each worksheet

Worksheets(1).Select     Worksheets.Add     ActiveSheet.Name = sNewName

lRow = 2     For Each w In Worksheets         If w.Name <> sNewName Then             'Comment out the following line if you don't want to             'include worksheet names in the summary sheet             Cells(lRow, 1) = w.Name             'If you commented out the previous line, make a change             'in the following line: change (lRow, 2) to (lRow, 1)

w.Range(sRange).Copy Cells(lRow, 2)

lRow = lRow + 1         End If     Next w End Sub

Beachten Sie, dass am Anfang des Makros zwei Variablen (sNewName und sRange) festgelegt sind. Diese stellen den Namen dar, den Sie für das vom Makro erstellte neue zusammenfassende Arbeitsblatt verwenden möchten, und den Zellenbereich, den Sie aus jedem Arbeitsblatt kopieren möchten.

Das Makro aktiviert dann das erste Arbeitsblatt in der Arbeitsmappe und fügt ein neues Arbeitsblatt hinzu, das zur Zusammenfassung verwendet werden soll. Diesem Arbeitsblatt wird der Name zugewiesen, den Sie in der Variablen sNewName angegeben haben. Das Makro durchläuft dann eine Schleife und überprüft jedes der anderen Arbeitsblätter. Solange das Arbeitsblatt nicht das zusammenfassende ist, wird der Name des Arbeitsblatts in Spalte A des Zusammenfassungsblatts eingefügt und der in der Variablen sRange (A146: O146) angegebene Bereich wird ab Spalte B in das zusammenfassende Arbeitsblatt kopiert .

Der Makroansatz ist schnell und einfach. Wenn Sie Ihr Zusammenfassungsblatt jemals wiederholen müssen, löschen Sie einfach das alte und führen Sie das Makro erneut aus.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (9812) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.