Kopieren Sie Excel-Arbeitsblatt und pflegen relative Zellreferenz in Formeln

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

• kopieren Sie das Blatt in eine neue Arbeitsmappe
• Aktivieren Sie das neue Blatt in der neuen Arbeitsmappe
• alle auswählen (Strg+A)
• mache ein Suchen / Ersetzen auf
  • suchen: [WorkbookA.xlsx]!
  • ersetzen:
• ersetze alle

Die vorzeichenlose Antwort direkt unter dieser ist diejenige, die für mich funktioniert hat, mit einer sehr geringen Variation.

1. Erstellen und speichern Sie eine Zieltabelle.

2. Verwenden Sie "Verschieben", "Kopieren" oder ziehen Sie Ihre Seite mit den Formeln in die neue Tabelle. Dadurch bleiben die Formeln auf der neuen Seite auf dem alten Arbeitsblatt. Speichern Sie dann die neue Tabelle an derselben Stelle wie das alte Arbeitsblatt.

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

4. Wählen Sie im daraufhin angezeigten Dialogfeld den Namen der Quelldatei aus und klicken Sie auf "Quelle ändern"."

5. Wählen Sie im Dialogfeld "Datei öffnen", das als nächstes angezeigt wird, den Namen der neuen Tabelle aus.

Klicken Sie auf Schließen und Sie sind fertig.

Oder einfach Folgendes tun:

Konvertieren Sie dies:

=database_feed!A1

Dazu:

=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")

Und geben Sie den Namen des Referenzblattes in Zelle B1 ein. Jetzt müssen Sie nur noch eine Zelle aktualisieren, wenn Sie in die neue Tabelle kopiert werden.

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

1. Kopieren Sie die Blätter aus der Originaldatei (Original.xlsx) in die neue Excel-Datei (Neu.xlsx) wie gewohnt. 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 (Neues.xlsx).

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

4. Wählen Sie im Popup "Ändern Quelle..."

5. Suchen Sie die Datei (Neu.xlsx) und klicken Sie auf Öffnen.

Alle Verweise auf das original (original.xlsx) wird entfernt.

, GETAN!

Der folgende Code kann an Ihre Bedürfnisse angepasst werden. Es nimmt alle Formeln aus dem Blatt auf wb1 und wendet sie auf ein Blatt in einer neuen Arbeitsmappe an. Die Formeln werden als Strings angewendet, sodass keine Verweise auf die ursprüngliche Arbeitsmappe eingefügt werden. Außerdem ist dieser Code super schnell, da er die Zwischenablage nicht verwendet und keine Schleife durch 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

• Arbeitsblatt in 'WorkbookB kopieren.xlsx'
• Arbeitsblatt in der neuen Datei öffnen
• alle auswählen
• gehe zum Menü Daten, klicke auf Links bearbeiten
• Links bearbeiten, so dass der Link zur alten Datei nun ein Link zur derzeit geöffnete Datei

Das funktioniert bei mir.

Wenn Sie dies automatisch tun müssen, weil Sie Blätter in einem VBA-Programm abrufen. Benutze 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 die im Quellbuchnamen gefundene Zeichenfolge."so passen Sie die alten Links, die Sie ersetzen möchten. Sie können diesen if-Block entfernen, wenn Sie alle Links ersetzen möchten.

Beide Arbeitsmappen müssen geöffnet sein, damit dies funktioniert. Sie führen dieses Makro aus und es kopiert workbookA!sheet1 nach workbookB!sheet1 und ersetzt dann alle workbookA Referenzen. Es ist grob, aber es funktioniert. Sie können den Code natürlich so ändern, dass er zu Ihrem WorkbookA passt.xlsx-Namen, stellen Sie jedoch sicher, dass sie die richtige Erweiterung haben und in Anführungszeichen stehen.

Oh, um ein Makro zu erstellen, falls Sie es nicht wissen, drücken Sie alt + F11, um den Visual Basic-Editor aufzurufen. Klicken Sie dann mit der rechten Maustaste auf WBA insert - module und kopieren Sie den folgenden 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 wird, liegt es wahrscheinlich daran, dass Makros nicht aktiviert sind, also speichern Sie es und öffnen Sie es erneut, und wenn es aufgefordert wird, 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 habe dies erreicht, indem ich die Zellen wie gewohnt in das neue Arbeitsblatt kopiert und dann nach einem Ersatz gesucht habe, um den alten Dateipfad in den Formeln zu entfernen.

Kein VB erforderlich!

Ich hatte ein ähnliches problem. Der Grund, warum die Formeln mit dem Link zu WBA eingefügt wurden, war, dass die Registerkarte (Blatt), an der ich in WBA arbeitete, anders benannt wurde als in WBB. Für mich war es immer "das letzte Blatt", aber eines hieß "MinFlow" und das andere "NormalFlow". Ich habe beide in "Ergebnisse"umbenannt und das Kopieren/Einfügen funktionierte so, wie ich es wollte - ein "relatives Einfügen".

Wählen Sie Zellen aus, die Sie verschieben möchten. Versuchen Sie nun, sie per Drag & Drop in ein anderes Arbeitsblatt (andere Registerkarte) zu verschieben.

Ich weiß, es scrollt. Hier ist der knifflige Teil: Drücken Sie einfach cmd (mac) oder alt (win) und lassen Sie die Zellen auf eine andere Registerkarte fallen.

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

1. Kopieren Sie wie gewohnt über die Zellen.
2. Wählen Sie in der Formel den Text aus, der ihn mit der vorherigen Arbeitsmappe verknüpft [WorkbookA.xlsx].
3. Wählen Sie alle Zellen aus, die Sie ändern möchten, und drücken Sie CTRL+F und wählen Sie die Registerkarte Ersetzen.
4. Ersetze [WorkbookA.xlsx] mit leerem Leerzeichen (aka schreibe nichts in das Feld Replace with, drücke Replace All.

Voila, es ist geschafft.

Ein weiterer 'Trick' - Ersetzen Sie vor dem Kopieren des Quellarbeitsblatts alle Formelqualifikatoren = durch andere Zeichen (z. B. ###=).

Kopiere das Arbeitsblatt und ersetze nach dem Kopieren das Formelqualifikator zurück (ersetze ###= durch =).

Stellen Sie sicher, dass alle Blattverweise innerhalb der Formauls vor dem Referenzierungsblatt auch in das neue Blatt kopiert werden.

1. Kopieren Sie das Blatt wie gewohnt. (rechtsklick auf Tab und gehen Sie zu 'Verschieben oder kopieren') Dies ist für die Formatierung.

2. Kopieren Sie alle Zellen aus dem Originalblatt (mit Strg+Ein oder links oben Dreieck und Strg+C)

3. Als Werte in die neue Arbeitsmappe einfügen (über" Step-1 " - Blatt) (Optionen einfügen>123)

Öffnen Sie beide Arbeitsmappen.  In der Quell-Arbeitsmappe (WorkbookA.xlsx), wählen Sie 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 "die Option" WorkbookB.xlsx "in der Dropdown-Liste" Buchen, wählen Sie aus, wo in diesem Buch Sie es ablegen möchten, und aktivieren Sie "Kopie erstellen".  (Und klicken Sie auf "OK".)

Erstellen Sie eine Kopie des Blattes, aus dem Sie Blätter verschieben möchten, in diesem Fall WorkbookA.xlsx. Benennen Sie es um, um "Kopie von WorkbookA" zu sagen.xlsx". Öffnen Sie nun diese neue Arbeitsmappe sowie die Arbeitsmappe, in die Sie das Blatt verschieben möchten, in das sich in diesem Fall WorkbookB.xlsx. Klicken Sie mit der rechten Maustaste auf Blätter in der von Ihnen erstellten Arbeitsmappe kopieren, dh Kopie von WorkbookA.xlsx und wählen Sie "Verschieben oder kopieren" und verschieben Sie diese Blätter in WorkbookB.xlsx. Du bist fertig !