Skopiuj arkusz w Excelu i zachowaj względne odniesienie do komórek w formułach

W wielu przypadkach łatwiej mi było wykonać następujące czynności:

• skopiować arkusz do nowego skoroszytu
• aktywować nowy arkusz w nowym skoroszycie
• wybrać wszystkie (Ctrl+A)
• znaleźć/wymienić na
• znaleźć: [WorkbookA.xlsx]!
• wymienić: <puść puste miejsce>
• zmień wszystkie

Spróbuj użyć Ctrl + ~, aby wyświetlić formuły. Następnie użyj Ctrl + A, aby zaznaczyć wszystko, skopiuj to, a następnie wklej do notatnika.

Na koniec skopiuj to z notatnika i wklej do innego skoroszytu.

Nieopodpisana odpowiedź tuż pod tą jest tą, która zadziałała dla mnie, z bardzo niewielkim zróżnicowaniem.

1. Stwórz i zapisz docelowy arkusz kalkulacyjny.

2. Użyj “przenieś”, “skopiuj” lub przeciągnij stronę z formułami do nowego arkusza kalkulacyjnego. W ten sposób formuły na nowej stronie zostaną przeniesione do starego arkusza. Następnie zapisz nowy arkusz kalkulacyjny w tym samym miejscu co stary arkusz.

3. Następnie przejdź do zakładki Data > kliknij Edit Links. Opcja ta nie będzie aktywna, jeżeli na stronie nie będzie linków.

4. W oknie dialogowym, które się pojawi, wybierz nazwę pliku źródłowego i kliknij “Change Source.”

5. W następnym oknie dialogowym, które się pojawi, wybierz nazwę nowego arkusza kalkulacyjnego.

Kliknij przycisk Close i gotowe.

Albo po prostu wykonaj następujące czynności:

Przekształć to:

=database_feed!A1

na to:

=INDIRECT("database_feed!A1")

i nie wprowadzaj więcej zmian do swoich referencji podczas kopiowania między arkuszami.

Jeśli nie masz wielu referencji, inną alternatywą byłoby użycie

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

i podanie nazwy arkusza referencyjnego w komórce B1. Teraz masz tylko jedną komórkę do aktualizacji po skopiowaniu do nowego arkusza kalkulacyjnego.

Poniższy kod może być dostosowany do Twoich potrzeb. Przyjmuje on wszystkie formuły z arkusza on wb1 i stosuje je do arkusza w nowym podręczniku. Formuły są stosowane jako Strings, więc nie ma wstawiania odniesień do oryginalnego skoroszytu. Ponadto, kod ten jest super szybki, ponieważ nie korzysta ze schowka i nie wymaga zapętlania komórek.

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

Ponieważ 99% z nich nie odpowiedziało nawet na oryginalne pytanie, oto właściwa odpowiedź.

1. Skopiuj arkusze z oryginalnego pliku (Original.xlsx) do nowego pliku Excela (New.xlsx), jak zwykle. Generalnie, klikam prawym przyciskiem myszy na nazwę i wybieram “Przesuń lub skopiuj…”.

2. Zapisz drugi - nowo utworzony plik (New.xlsx).

3. W nowym pliku, pod Danymi, kliknij “Edit Links”

4. W oknie pop-up, wybierz “Change Source…”

5. Znajdź plik (New.xlsx) i kliknij Open.

Wszystkie odwołania do oryginału (Original.xlsx) zostaną usunięte.

DONE!

• skopiować arkusz do ‘WorkbookB.xlsx’
• otworzyć arkusz w nowym pliku
• wybrać wszystkie
• przejść do menu Dane, kliknąć na edit links
• edytować linki tak, aby link do starego pliku był teraz linkiem do obecnie otwartego pliku

To działa dla mnie.

Jeśli musisz zrobić to automatycznie, ponieważ wciągasz arkusze w programie VBA. Użyj tego:

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

Wystarczy zmienić “String found in source book name.” aby dopasować stare linki, które chcesz wymienić. Możesz to usunąć, jeśli chcesz zastąpić wszystkie linki.

Kolejny “trick” - przed skopiowaniem arkusza źródłowego należy zastąpić wszystkie kwalifikatory formuły = innym zestawem znaków (powiedzmy ###=).

Skopiuj arkusz nad, a następnie po skopiowaniu ponownie zamień kwalifikator formuły (zastępując ###= =).

Upewnij się, że wszelkie odniesienia do arkuszy w ramach formuł są również kopiowane do nowego arkusza przed arkuszem odniesienia.

Oba podręczniki muszą być otwarte, żeby to zadziałało Uruchomisz to makro, a ono skopiuje workbookA!sheet1 do workbookB!sheet1, a następnie zastąpi wszystkie odnośniki workbookA. To jest surowe, ale działa Możesz oczywiście zmienić kod tak, aby pasował do Twoich nazw WorkbookA.xlsx, ale upewnij się, że mają one odpowiednie rozszerzenie i pozostają w cudzysłowie.

Oh, aby zrobić makro, w przypadku gdy nie wiesz, naciśnij alt + F11, aby wywołać Visual Basic Editor. Następnie kliknij prawym przyciskiem myszy na WBA insert - module i skopiuj i wklej poniższy kod do modułu. Następnie naciśnij F5, aby uruchomić makro. Jeśli makro nie uruchomi się, to prawdopodobnie dlatego, że makra nie są włączone, więc kontynuuj, zapisz je i ponownie otwórz, a kiedy pojawi się monit o włączenie makr, włącz je.

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

Miałam podobny problem. Powodem, dla którego formuły zostały wklejone z linkiem do WBA było to, że zakładka (arkusz), nad którą pracowałem w WBA, została nazwana inaczej niż w WBBB. Dla mnie zawsze był to “ostatni arkusz”, ale jeden z nich nosił nazwę ‘MinFlow’, a drugi ‘NormalFlow’. Zmieniłem nazwę na ‘Wyniki’ i kopia/klej działała tak jak chciałem - “względna pasta”.

Wybierz komórki, które chcesz przenieść. Teraz spróbuj je przenieść, przeciągając n drop na inny arkusz (inną kartę).

Wiem, przewija się. Tutaj jest trudna część: wystarczy nacisnąć cmd (mac) lub alt (win) i będzie można upuścić komórki do innej zakładki.

Osiągnąłem to kopiując komórki do nowego arkusza jak zwykle, a następnie robiąc find a replace w celu usunięcia starej ścieżki pliku we wzorach.

Np. jeśli pierwszy wzór to =J2 a to staje się =[filepath]J2, to po prostu znajdź i zamień cały nowy arkusz kalkulacyjny na [filepath] i zamień na nic. To usuwa go i przywraca formułę do =J2.

Nie wymaga VB!

Witam, oto proste rozwiązanie tego problemu:

1. Skopiuj nad komórkami jak zwykle.
2. We wzorze zaznacz i skopiuj tekst łączący go z poprzednim workbookiem [WorkbookA.xlsx].
3. Zaznacz wszystkie komórki, które chcesz zmienić, a następnie naciśnij klawisz CTRL+F i wybierz zakładkę zamiany.
4. Zastąpić [WorkbookA.xlsx] pustym miejscem (aka nie wpisywać nic w pole Replace with, nacisnąć Replace All.

Voila - to jest zrobione.

1. Skopiuj arkusz jak zwykle. (kliknij prawym przyciskiem myszy na zakładkę i przejdź do ‘przenieś lub skopiuj’) To jest do formatowania.

2. Skopiuj wszystkie komórki z oryginalnego arkusza (używając Ctrl+A lub górnego lewego trójkąta i Ctrl+C)

3. Wklej jako wartość do nowego skoroszytu (nad arkuszem “krok 1”) (Opcje wklejania →123)

Otwórz oba workbooks.  w oknie dialogowym “Move or Copy” wybierz arkusz, który chcesz skopiować (WorkbookA.xlsx); kliknij prawym przyciskiem myszy na zakładce arkusza i wybierz “Move or Copy…”.  w oknie dialogowym “Move or Copy” wybierz “WorkbookB.xlsx” w rozwijanej liście “To book”, wybierz miejsce, w którym chcesz go umieścić i zaznacz “Create a copy”.  (I kliknij “OK”).

Zrób kopię arkusza, z którego chcesz przenieść arkusze, a w tym przypadku będzie to WorkbookA.xlsx. Zmień jego nazwę na “Kopia WorkbookA.xlsx”. Teraz otwórz ten nowy skoroszyt, jak również skoroszyt, do którego chcesz przenieść arkusz, a w tym przypadku będzie to WorkbookB.xlsx. Kliknij prawym przyciskiem myszy na arkusze w utworzonej kopii skoroszytu, tzn. Kopii WorkbookA.xlsx i wybierz “przenieś lub skopiuj”, a następnie przenieś te arkusze do WorkbookB.xlsx. Skończyłeś!