Skopiuj arkusz programu Excel i zachowaj względne odwołanie do komórki w formułach

Kolejny problem z kopiowaniem w programie Excel:

Jak mogę skopiować arkusz z WorkbookA.xlsx do WorkbookB.xlsx bez skopiowanego arkusza roboczego wciąż odwołującego się do WorkbookA.xlsx, np. Formuła =B!23staje się =[WorkbookA.xlsx]!B!23po skopiowaniu.

Chcę zachować „względne” odwołania do komórek zamiast „absolutnych” odwołań do komórek (wymyślę tę terminologię w świecie Excela, jeśli jeszcze nie istnieje).

Inną możliwą alternatywą, której nie mogę uruchomić, jest opcja wklejania tylko „wartości” komórek. Excel traktuje „wartości” jak wartości obliczone, a nie rzeczywiste formuły w komórce. Jeśli wybiorę formułę wklejania, nadal będzie zawierać absolutne odniesienia.

Więcej informacji o tym, dlaczego potrzebuję tego: Mam codzienne użytkowe oprogramowanie XLSX. Ciągle musimy dokonywać „uaktualnień” do tego pliku xlsx, aby jedna osoba mogła utworzyć kopię i jego zmiany w jednym arkuszu. Jednocześnie inna osoba może również wprowadzać zmiany w innym arkuszu. Biorąc pod uwagę, że te arkusze nie mają zależnych komórek na innych arkuszach , takich jak raport podsumowujący, pożądane jest, aby po prostu skopiować i scalić arkusze z powrotem do oryginalnego pliku xlsx. Ale odniesienie „bezwzględne” sprawia wiele problemów.

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

Na koniec skopiuj go z notatnika i wklej do drugiego skoroszytu.

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

• skopiuj arkusz do nowego skoroszytu
• aktywuj nowy arkusz w nowym skoroszycie
• wybierz wszystko ( Ctrl+ A)
• znajdź / zamień na
  • odnaleźć: [WorkbookA.xlsx]!
  • zamień: <pozostaw puste>
• Zamień wszystko

Podpisana poniżej odpowiedź bez odpowiedzi jest tą, która działała dla mnie, z bardzo niewielkimi różnicami.

1. Utwórz i zapisz docelowy arkusz kalkulacyjny.

2. Użyj „przenieś”, „skopiuj” lub przeciągnij stronę z formułami do nowego arkusza kalkulacyjnego. Pozostawia to formuły na nowej stronie wskazujące na stary arkusz roboczy. Następnie zapisz nowy arkusz kalkulacyjny w tej samej lokalizacji, co stary arkusz.

3. Następnie przejdź do zakładki Dane> kliknij Edytuj łącza. Opcja nie będzie aktywna, chyba że na stronie znajdują się linki.

4. W wyświetlonym oknie dialogowym wybierz nazwę pliku źródłowego i kliknij „Zmień źródło”.

5. W oknie dialogowym otwartego pliku, które pojawi się obok, wybierz nazwę nowego arkusza kalkulacyjnego.

Kliknij Zamknij i gotowe.

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

Konwertuj to:

=database_feed!A1

do tego:

=INDIRECT("database_feed!A1")

i żadnych zmian w odniesieniach podczas kopiowania między arkuszami.

Jeśli nie odwołujesz się do wielu arkuszy, możesz użyć innej alternatywy

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

i wprowadź nazwę arkusza referencyjnego w komórce B1. Teraz masz tylko jedną komórkę do zaktualizowania po skopiowaniu do nowego arkusza kalkulacyjnego.

Poniższy kod można dostosować do własnych potrzeb. Pobiera wszystkie formuły z arkusza wb1i stosuje je do arkusza w nowym skoroszycie. Formuły są stosowane jako Strings, więc nie ma wstawiania odniesień do oryginalnego skoroszytu. Ponadto ten kod jest super szybki, ponieważ nie korzysta ze schowka i nie wymaga pętli w komórkach.

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% odpowiedzi nawet nie dotyczyło pierwotnego pytania, oto właściwa odpowiedź.

1. Skopiuj arkusze z oryginalnego pliku (Original.xlsx) do nowego pliku Excel (New.xlsx), jak zwykle. Zasadniczo klikam nazwę prawym przyciskiem myszy i wybieram „Przenieś lub skopiuj ...”.

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

3. W nowym pliku w obszarze Dane kliknij „Edytuj łącza”

4. W wyskakującym okienku wybierz „Zmień źródło ...”

5. Znajdź plik (New.xlsx) i kliknij Otwórz.

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

GOTOWY!

• skopiuj arkusz do „WorkbookB.xlsx”
• otwórz arkusz roboczy w nowym pliku
• Zaznacz wszystko
• przejdź do menu Dane, kliknij edytuj linki
• edytuj linki, aby link do starego pliku był teraz linkiem do aktualnie otwartego pliku

To działa dla mnie.

Oba skoroszyty muszą być otwarte, aby to działało . Uruchom to makro, a skopiuje ono workbookA!sheet1, workbookB!sheet1a następnie zastąpi wszystkie workbookAodniesienia. Jest surowy, ale działa . Możesz oczywiście zmienić kod, aby pasował do nazw WorkbookA.xlsx, ale upewnij się, że mają poprawne rozszerzenie i pozostają w cudzysłowie.

Aha, aby utworzyć makro, na wypadek, gdybyś nie wiedział, naciśnij, alt + F11aby wywołać Edytor Visual Basic. Następnie kliknij prawym przyciskiem myszy WBA insert - modulei skopiuj i wklej poniższy kod do modułu. Następnie naciśnij, F5aby uruchomić makro. Jeśli makro nie uruchomi się, to prawdopodobnie dlatego, że makra nie są włączone, więc zapisz je i otwórz ponownie, a gdy 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

Osiągnąłem to, kopiując komórki do nowego arkusza w normalny sposób, a następnie wykonując operację zastępowania w celu usunięcia starej ścieżki pliku z formuł.

Np. Jeśli pierwsza formuła jest =J2i tak się stanie =[filepath]J2, po prostu znajdź i zamień cały nowy arkusz kalkulacyjny na [filepath]i zamień na nic. Spowoduje to usunięcie i przywrócenie formuły do =J2.

Nie wymaga VB!

Miałem podobny problem. Powodem wklejenia formuł z linkiem do WBA było to, że karta (arkusz), nad którą pracowałem w WBA, miała inną nazwę niż ta w WBB. Dla mnie zawsze był to „ostatni arkusz”, ale jeden nazywał się „MinFlow”, a drugi „NormalFlow”. Zmieniłem nazwę na „Wyniki”, a kopiowanie / wklejanie działało tak, jak chciałem - „wklejanie względne”.

Wybierz komórki, które chcesz przenieść. Teraz spróbuj przenieść je, przeciągając i upuszczając w innym arkuszu (inna karta).

Wiem, przewija się. Oto trudna część: wystarczy nacisnąć cmd (mac) lub alt (win), a to pozwoli ci upuścić komórki do innej karty.

Cześć, oto proste rozwiązanie tego problemu:

1. Skopiuj komórki jak zwykle.
2. W formule wybierz i skopiuj tekst łączący go z poprzednim skoroszytem [WorkbookA.xlsx].
3. Wybierz wszystkie komórki, które chcesz zmienić, a następnie naciśnij CTRL+Fi wybierz kartę zamień.
4. Zamień [WorkbookA.xlsx] na puste miejsce (inaczej nic nie pisz w Replace withpolu, naciśnij Replace All.

Voila - gotowe.

Kolejna „sztuczka” - przed skopiowaniem źródłowego arkusza roboczego zamień wszystkie kwalifikatory formuł na =inny zestaw znaków (powiedzmy ###=).

Skopiować arkusz skończy, to po kopii, wymienić tylną formuła kwalifikator (zastępujący ###=z =).

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

1. Skopiuj arkusz jak zwykle. (kliknij prawym przyciskiem myszy, aby przejść do zakładki i przejść do „przenieś lub skopiuj”). Służy do formatowania.

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

3. Wklej jako wartości do nowego skoroszytu (na arkuszu „krok 1”) (Opcje wklejania> 123)

Jeśli musisz to zrobić automatycznie, ponieważ pobierasz 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ć „Ciąg znaleziony w nazwie książki źródłowej”. aby dopasować stare linki, które chcesz zastąpić. Możesz usunąć to, jeśli blok, jeśli chcesz zastąpić wszystkie linki.

Otwórz oba skoroszyty. W źródłowym skoroszycie (WorkbookA.xlsx) wybierz arkusz, który chcesz skopiować. Kliknij kartę arkusza prawym przyciskiem myszy i wybierz „Przenieś lub skopiuj…”. W oknie dialogowym „Przenieś lub skopiuj” wybierz „WorkbookB.xlsx” z menu „Aby zarezerwować”, wybierz miejsce w tej książce, w którym chcesz ją umieścić, i zaznacz „Utwórz kopię”. (I kliknij „OK”.)

Zrób kopię arkusza, z którego chcesz przenieść arkusze, z czego w takim przypadku byłby WorkbookA.xlsx. Zmień nazwę, aby powiedzieć „Kopia skoroszytuA.xlsx”. Teraz otwórz ten nowy skoroszyt, a także skoroszyt, do którego chcesz przenieść arkusz, do którego w tym przypadku będzie WorkbookB.xlsx. Kliknij prawym przyciskiem myszy arkusze w utworzonym skoroszycie kopiowania, tj. Kopiuj WorkbookA.xlsx i wybierz „przenieś lub skopiuj”, a następnie przenieś te arkusze do WorkbookB.xlsx. Gotowe!