Excel-Arbeitsblatt kopieren und relativen Zellbezug in Formeln beibehalten

Ich habe es in vielen Fällen einfacher gefunden, Folgendes zu tun:

• das Blatt in eine neue Arbeitsmappe kopieren
• das neue Blatt in der neuen Arbeitsmappe aktivieren
• alles auswählen (Strg+A)
• bei suchen/ersetzen - finden: [WorkbookA.xlsx]!
• ersetzen: <leer lassen>
• alle ersetzen

Versuchen Sie, die Formeln mit Strg + ~ anzuzeigen. Verwenden Sie dann Strg + A, um alles auszuwählen, kopieren Sie es und fügen Sie es dann in den Notizblock ein

Kopieren Sie es schließlich aus dem Notizblock und fügen Sie es in Ihre andere Arbeitsmappe ein.

Die unsignierte Antwort direkt unter dieser ist die, die für mich funktioniert hat, mit einer sehr leichten Abweichung.

1. Erstellen und speichern Sie eine Zieltabelle.

2. Verwenden Sie “Verschieben”, “Kopieren” oder ziehen Sie Ihre Seite mit den Formeln in das neue Arbeitsblatt. Dadurch bleiben die Formeln auf der neuen Seite und zeigen auf das alte Arbeitsblatt. Speichern Sie dann das neue Arbeitsblatt an der gleichen Stelle wie das alte Arbeitsblatt.

3. Gehen Sie dann zur Registerkarte Daten > klicken Sie auf Links bearbeiten. Die Option ist nur dann aktiv, wenn es auf der Seite Links gibt.

4. Wählen Sie im daraufhin erscheinenden Dialogfeld den Namen der Quelldatei und klicken Sie auf “Quelle ändern”.

5. Wählen Sie im daraufhin erscheinenden Dialogfeld “Datei öffnen” den Namen des neuen Arbeitsblatts.

Klicken Sie auf “Schließen”, und Sie sind fertig.

Oder machen Sie einfach Folgendes:

Konvertieren Sie dies:

=database_feed!A1

in dies:

=INDIRECT("database_feed!A1")

und keine Änderungen mehr an Ihren Referenzen, wenn Sie zwischen Arbeitsblättern kopieren.

Wenn Sie nicht viele Blätter referenziert haben, wäre eine andere Alternative,

=INDIRECT("'"&B1&"'!A1")

zu verwenden und den Namen des Referenzblattes in Zelle B1 einzutragen. Jetzt haben Sie nur noch eine Zelle zu aktualisieren, wenn Sie sie in das neue Arbeitsblatt kopieren.

Der untenstehende Code kann an Ihre Bedürfnisse angepasst werden. Er übernimmt alle Formeln aus dem Blatt auf wb1 und wendet sie auf ein Blatt in einer neuen Arbeitsmappe an. Die Formeln werden als Strings angewendet, es werden also keine Verweise auf die ursprüngliche Arbeitsmappe eingefügt. Außerdem ist dieser Code superschnell, da er nicht die Zwischenablage verwendet und kein Durchschleifen von Zellen erfordert.

Sub copyformulas()

Dim wb1 As Workbook, wb2 As Workbook
Dim s1 As Worksheet, s2 As Worksheet
Dim formArr() As Variant

Set wb1 = ThisWorkbook
Set s1 = wb1.Sheets("Sheet1")
Set wb2 = Workbooks.Add
Set s2 = wb2.Sheets("Sheet1")

formArr = s1.UsedRange.Formula
s2.Range("A1").Resize(UBound(formArr, 1), UBound(formArr, 2)).Formula = formArr

End Sub

Da 99% der Antworten nicht einmal auf die ursprüngliche Frage eingingen, ist hier die richtige Antwort.

1. Kopieren Sie die Blätter aus der Originaldatei (Original.xlsx) in die neue Excel-Datei (New.xlsx), wie Sie es normalerweise tun würden. Im Allgemeinen klicke ich mit der rechten Maustaste auf den Namen und wähle “Verschieben oder Kopieren…”.

2. Speichern Sie die zweite - neu erstellte Datei (New.xlsx).

3. Klicken Sie in der neuen Datei unter Daten auf “Links bearbeiten”

4. Wählen Sie im Popup-Fenster “Quelle ändern…”

5. Suchen Sie die Datei (New.xlsx) und klicken Sie auf “Öffnen”.

Alle Verweise auf das Original (Original.xlsx) werden entfernt.

FERTIG!

• Arbeitsblatt nach ‘WorkbookB.xlsx’ kopieren
• Arbeitsblatt in der neuen Datei öffnen
• alles auswählen
• zum Menü Daten gehen, auf Links bearbeiten klicken
• Links bearbeiten, so dass der Link zur alten Datei jetzt ein Link zur gegenwärtig geöffneten Datei ist

Das funktioniert bei mir.

Wenn Sie dies automatisch tun müssen, weil Sie Blätter in einem VBA-Programm einziehen. Verwenden Sie dies:

Public Sub ChangeSource()
'
' ChangeSource Macro
' Edit the links to point to the current workbook.
'
    Dim allLinks As Variant
    allLinks = ThisWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(allLinks) Then
        Dim eachLink As Long
        For eachLink = 1 To UBound(allLinks)
            If InStr(3, "String found in source book name.", allLinks(eachLink)) Then
                ThisWorkbook.ChangeLink Name:=allLinks(eachLink), NewName:=ThisWorkbook.FullName, Type:=xlExcelLinks
            End If
        Next eachLink
    End If
End Sub

Ändern Sie einfach “String found in source book name”, um die alten Links, die Sie ersetzen möchten, anzupassen. Wenn Sie alle Links ersetzen möchten, können Sie den If-Block entfernen.

Ein weiterer ‘Trick’ - bevor Sie das Quellarbeitsblatt kopieren, ersetzen Sie alle Formelqualifier = durch einen anderen Zeichensatz (sagen wir ###=).

Kopieren Sie das Arbeitsblatt über, und ersetzen Sie nach dem Kopieren den Formelqualifier wieder (ersetzen Sie ###= durch =).

Stellen Sie sicher, dass alle Blattreferenzen innerhalb der Formblätter vor dem referenzierenden Blatt ebenfalls auf das neue Blatt kopiert werden.

Beide Arbeitsmappen müssen geöffnet sein, damit dies funktioniert. Wenn Sie dieses Makro ausführen, kopiert es workbookA!sheet1 nach workbookB!sheet1 und ersetzt dann alle Verweise auf workbookA. Es ist roh, aber es funktioniert. Sie können den Code natürlich so ändern, dass er mit Ihren WorkbookA.xlsx-Namen übereinstimmt, aber stellen Sie sicher, dass sie die richtige Erweiterung haben und in Anführungszeichen bleiben.

Oh, um ein Makro zu erstellen, drücken Sie, falls Sie es nicht wissen, Alt + F11, um den Visual Basic-Editor aufzurufen. Klicken Sie dann mit der rechten Maustaste auf WBA insert - module und kopieren Sie den untenstehenden Code und fügen Sie ihn in das Modul ein. Drücken Sie dann F5, um das Makro auszuführen. Wenn das Makro nicht ausgeführt werden kann, liegt das wahrscheinlich daran, dass Makros nicht aktiviert sind. Speichern Sie es also und öffnen Sie es erneut, und wenn es Sie auffordert, Makros zu aktivieren, aktivieren Sie sie.

Sub copysheetremoveWBref()

    Application.ScreenUpdating = False

    'activate WBA
    Application.Workbooks("workbooka.xlsx").Activate
    'Select WBA Sheet1
    Application.Workbooks("workbooka.xlsx").Sheets("Sheet1").Select
    'copy WBA!sheet1 to WBB!sheet1
    Sheets("Sheet1").copy Before:=Workbooks("WorkbookB.xlsx").Sheets("sheet2")
    'find WBA references and remove them
    Cells.Replace What:="=[workbookA.xlsx]", Replacement:="=", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Application.ScreenUpdating = True

End Sub

Ich hatte ein ähnliches Problem. Der Grund dafür, dass die Formeln mit dem Link zur WBA eingefügt wurden, war, dass die Registerkarte (das Blatt), an der ich in der WBA arbeitete, einen anderen Namen hatte als in der WBB. Für mich war es immer “das letzte Blatt”, aber eines hieß ‘MinFlow’ und das andere ‘NormalFlow’. Ich benannte beide in “Ergebnisse” um, und das Kopieren/Einfügen funktionierte so, wie ich es wollte - ein “relatives Einfügen”.

Markieren Sie die Zellen, die Sie verschieben möchten. Versuchen Sie nun, sie per Drag & Drop auf ein anderes Arbeitsblatt (andere Registerkarte) zu verschieben.

Ich weiß, es scrollt. Jetzt kommt der knifflige Teil: Drücken Sie einfach cmd (mac) oder alt (win), und schon können Sie die Zellen auf ein anderes Register ziehen.

Ich habe dies erreicht, indem ich die Zellen wie gewohnt in das neue Arbeitsblatt kopiert und dann eine Suche und Ersetzung durchgeführt habe, um den alten Dateipfad in den Formeln zu entfernen.

Wenn z.B. die erste Formel =J2 lautet und diese zu =[filepath]J2 wird, dann führen Sie einfach eine Suche und Ersetzung des gesamten neuen Arbeitsblatts für [filepath] durch und ersetzen Sie es durch nichts. Dadurch wird sie gelöscht und die Formel wird wieder zu =J2.

Kein VB erforderlich!

Hallo, hier ist eine einfache Lösung für dieses Problem:

1. Kopieren Sie die Zellen wie üblich:
2. Markieren und kopieren Sie in der Formel den Text, der sie mit der vorherigen Arbeitsmappe verknüpft [ArbeitsmappeA.xlsx].
3. Markieren Sie alle Zellen, die Sie ändern möchten, und drücken Sie CTRL+F und wählen Sie die Registerkarte Ersetzen.
4. Ersetzen Sie [ArbeitsmappeA.xlsx] durch ein leeres Feld (d.h. schreiben Sie nichts in das Feld Replace with, drücken Sie Replace All.

Voila - fertig.

1. Kopieren Sie das Blatt wie gewohnt. (Klicken Sie mit der rechten Maustaste auf die Registerkarte und gehen Sie auf ‘Verschieben oder Kopieren’) Dies ist für die Formatierung.

2. Alle Zellen vom Originalblatt kopieren (mit Strg+A oder Dreieck oben links und Strg+C)

3. Als Werte in die neue Arbeitsmappe einfügen (über “Schritt 1”-Blatt) (Einfügeoptionen>123)

Öffnen Sie beide Arbeitsmappen.  Wählen Sie in der Quell-Arbeitsmappe (WorkbookA.xlsx) das Blatt aus, das Sie kopieren möchten.  Klicken Sie mit der rechten Maustaste auf die Registerkarte “Blatt” und wählen Sie “Verschieben oder Kopieren…”.  Wählen Sie im Dialogfeld “Verschieben oder Kopieren” in der Dropdown-Liste “In Buch” die Option “WorkbookB.xlsx”, wählen Sie aus, wo in diesem Buch Sie es ablegen möchten, und markieren Sie “Erstellen a kopieren”.  (Und klicken Sie auf “OK”.)

Erstellen Sie eine Kopie des Blattes, aus dem Sie die Blätter verschieben möchten. In diesem Fall wäre das WorkbookA.xlsx. Benennen Sie es um in “Kopie von WorkbookA.xlsx”. Öffnen Sie nun diese neue Arbeitsmappe sowie die Arbeitsmappe, in die Sie das Blatt verschieben möchten, in dem in diesem Fall WorkbookB.xlsx stehen würde. Klicken Sie mit der rechten Maustaste auf Blätter in der von Ihnen erstellten Kopie der Arbeitsmappe, d.h. “Kopie von WorkbookA.xlsx”, und wählen Sie “Verschieben oder Kopieren” und verschieben Sie dann diese Blätter nach WorkbookB.xlsx. Sie sind fertig !