Excel-Formeln mit VBA in ein anderes Tabellenblatt kopieren

Um Formeln mithilfe von VBA von einem Excel-Arbeitsblatt in ein anderes zu kopieren, weisen Sie den Quellbereich FormulaR1C1einem gleich großen Zielbereich zu. Dadurch wird das Formelmuster übertragen, ohne die Zellformatierung zu kopieren, und relative Bezüge werden an die Zielzellen angepasst. Das untenstehende Makro verwendet Arbeitsblattnamen und ist daher unabhängig vom aktuell aktiven Arbeitsblatt.

Bevor Sie beginnen

Verwenden Sie die Desktop-Version von Excel. Excel für das Web kann zwar Arbeitsmappen mit Makros öffnen, aber keine VBA-Makros erstellen, ausführen oder bearbeiten, wie in der Microsoft- Anleitung zu Excel für das Web erläutert . Überprüfen Sie die genauen Tabellennamen sowie die Quell- und Zieladressen. In diesem Beispiel befinden sich die Formeln in Tabelle A Source!C2:C10und werden in SummaryTabelle B ab Adresse C eingefügt C2.

Das Beispiel verwendet ThisWorkbookdie Arbeitsmappe, in der der Makrocode gespeichert ist. Belassen Sie das Makro in dieser Arbeitsmappe oder ändern Sie den Arbeitsmappenverweis, falls der Code an einem anderen Ort gespeichert ist. Die Tabellenblattnamen werden aus der WorksheetsSammlung der Arbeitsmappe ausgewählt; der Name in Anführungszeichen muss exakt mit dem Tabellenblatt übereinstimmen.

Formeln mit VBA in ein anderes Tabellenblatt kopieren

Öffnen Sie den Visual Basic Editor über die Registerkarte „Entwicklertools “ in Excel , wählen Sie „Visual Basic“ und anschließend „ Einfügen“ > „Modul“ . Fügen Sie die folgende Prozedur in das Modul ein:

Sub CopyFormulasToAnotherSheet()
    Dim wb As Workbook
    Dim sourceRange As Range
    Dim targetCell As Range
    Dim targetRange As Range

    Set wb = ThisWorkbook
    Set sourceRange = wb.Worksheets("Source").Range("C2:C10")
    Set targetCell = wb.Worksheets("Summary").Range("C2")
    Set targetRange = targetCell.Resize(sourceRange.Rows.Count, _
                                        sourceRange.Columns.Count)

    targetRange.FormulaR1C1 = sourceRange.FormulaR1C1
End Sub

Ändern Sie die Platzhalter Source, Summarydie Bereichsadressen und die Zieladressen entsprechend Ihrer Arbeitsmappe. targetCellist die erste Zielzelle; Resizesorgt dafür, dass die Zielzelle dieselbe Höhe und Breite wie die Quellzelle hat, was für eine Zuweisung an mehrere Zellen erforderlich ist. Für eine einzelne Formel legen Sie die Quellzelle auf eine Zelle fest, z. B. C2.

Makro ausführen und speichern

1. Setzen Sie die Einfügemarke an eine beliebige Stelle CopyFormulasToAnotherSheetim Visual Basic Editor und drücken Sie F5 . Alternativ können Sie zu Excel zurückkehren, „Entwicklertools“ > „Makros“ auswählen , die Prozedur auswählen und auf „Ausführen“ klicken .
2. Überprüfen Sie die Zielzellen in Tabelle 1. SummaryBei einer Quellformel wie beispielsweise =A2*B2in Tabelle 2 ist Source!C2das kopierte Formelmuster in Tabelle 3 Summary!C2ebenfalls =A2*B2. Wenn die Formel weiterhin Werte aus dem Quellblatt lesen soll, qualifizieren Sie diese Verweise in der Quellformel, zum Beispiel mit =Source!A2*Source!B2.
3. Speichern Sie die Arbeitsmappe als Excel-Arbeitsmappe mit Makros ( .xlsm), damit die VBA-Prozedur erhalten bleibt. Wenn Excel nach der Makrosicherheit fragt, führen Sie nur Makros aus, denen Sie vertrauen.

Wählen Sie die richtige Methode für Ihre Formeln.

FormulaR1C1Die Formelstruktur wird kopiert. Ein Bezug zwei Spalten links von der Formelzelle bleibt auch im Zielbereich zwei Spalten links davon; absolute Bezüge bleiben unverändert. Dies ist nützlich, wenn Sie dasselbe Berechnungsmuster in einem entsprechenden Zielbereich verwenden möchten. Die Formatierung wird nicht kopiert. Enthält Ihr Quellbereich Konstanten oder leere Zellen, werden diese ebenfalls als Werte oder leere Zellen übertragen. Verwenden Sie daher einen Bereich, der nur die zu kopierenden Formeln enthält.

Für das normale Kopierverhalten von Excel – einschließlich Formeln und Formatierung – verwenden Sie Range.Copyein Ziel:

wb.Worksheets("Source").Range("C2:C10").Copy _
    Destination:=wb.Worksheets("Summary").Range("C2")

Dadurch werden der gesamte Zellinhalt und die Formatierung kopiert; Excel passt relative Bezüge wie beim normalen Kopieren an. Verwenden Sie diese Methode, wenn die Zielzelle mehr als nur Formeln enthalten soll. Microsoft dokumentiert sowohl die Eigenschaft FormulaR1C1 als auch die Methode Range.Copy .

Häufige Probleme beheben

• „Index außerhalb des gültigen Bereichs“: Überprüfen Sie, ob die Namen der Arbeitsblätter genau so geschrieben sind, wie sie auf den Registerkarten erscheinen, einschließlich der Leerzeichen.
• Die Formel verweist auf die falschen Zellen: FormulaR1C1 Sie enthält relative Bezüge zum Zieltabellenblatt. Fügen Sie der Formel einen Tabellenblattnamen hinzu, wenn sie weiterhin auf das Quelltabellenblatt verweisen soll.
• Der Code verwendet die falsche Arbeitsmappe: ThisWorkbook Er verweist auf die Datei, die das Makro enthält. Befindet sich der Code in einer persönlichen Makro-Arbeitsmappe, geben Sie wbstattdessen den Namen der geöffneten Arbeitsmappe an.
• Das Makro wird nicht angezeigt: Stellen Sie sicher, dass die Datei unter .xlsmeinem vertrauenswürdigen Namen gespeichert ist und Makros für diese Datei aktiviert sind. In der Makro-Anleitung von Microsoft wird erklärt, wie Sie die Registerkarte „Entwicklertools“ aktivieren und eine Prozedur ausführen.

Wenn Quell- und Zielnamen, Bereiche und das Referenzverhalten bewusst festgelegt werden, kann das Makro eine einzelne Formel oder einen ganzen Block kopieren, ohne dass Tabellenblätter ausgewählt oder die Zwischenablage verwendet werden muss.