Kopieer Excel-werkblad en handhaaf relatieve celverwijzing in formules

Ik heb het in veel gevallen makkelijker gevonden om het volgende te doen:

• kopieer het blad naar een nieuw werkboek
• activeer het nieuwe blad in het nieuwe werkboek
• selecteer alles (Ctrl+A)
• doe een vind/vervanging op
• zoek: [WorkbookA.xlsx]!
• vervang: \>
• alles vervangen

Probeer met Ctrl + ~ de formules weer te geven. Gebruik dan Ctrl + A om alles te selecteren, kopieer het en plak het in het notitieblok.

Tenslotte kopieer je het uit het notitieblok en plak je het in je andere werkboek.

Het ongesigneerde antwoord hier onder is het antwoord dat voor mij werkte, met een zeer kleine variatie.

1. 1. Maak een bestemmingsspreadsheet aan en sla deze op.
2. 1. Gebruik “verplaatsen”, “kopiëren”, of sleep je pagina met de formules naar de nieuwe spreadsheet. Hierdoor blijven de formules op de nieuwe pagina naar het oude werkblad wijzen. Sla dan de nieuwe spreadsheet op dezelfde plaats op als de oude.
3. 1. Ga dan naar het tabblad Gegevens en klik op Links bewerken. De optie is alleen actief als er links op de pagina staan.

4. Selecteer in het dialoogvenster dat verschijnt de naam van het bronbestand en klik op “Wijzig bron”.

5. Selecteer in het open-bestanddialoogvenster dat nu verschijnt de naam van het nieuwe werkblad.

Klik op Sluiten en u bent klaar.

Of doe gewoon het volgende:

Converteer dit:

=database_feed!A1

naar dit:

=INDIRECT("database_feed!A1")

en geen wijzigingen meer in uw referenties wanneer u tussen werkbladen kopieert.

Als u niet veel bladen heeft waarnaar wordt verwezen, zou een ander alternatief zijn om

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

te gebruiken en de naam van het referentieblad in de cel B1 in te voeren. Nu heb je slechts één cel om bij te werken wanneer je naar het nieuwe werkblad wordt gekopieerd.

De onderstaande code kan worden aangepast aan uw behoeften. Het neemt alle formules uit het blad op wb1 en past ze toe op een blad in een nieuw werkboek. De formules worden toegepast als Strings, dus er worden geen verwijzingen naar het originele werkboek ingevoegd. Ook is deze code supersnel omdat hij geen gebruik maakt van het klembord en geen looping door cellen vereist.

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

Aangezien 99% van de antwoorden niet eens de oorspronkelijke vraag beantwoordde, volgt hier het juiste antwoord.

1. 1. Kopieer de vellen van het oorspronkelijke bestand (Original.xlsx) naar het nieuwe Excel-bestand (New.xlsx) zoals u dat normaal gesproken zou doen. Over het algemeen klik ik met de rechtermuisknop op de naam en kies voor “Verplaatsen of kopiëren…”.
2. 1. Sla het tweede - nieuw aangemaakte bestand (New.xlsx) op.
3. 1. In het nieuwe bestand, onder Data, klik op “Edit Links”

4. Kies in de pop-up “Change Source…”

5. Zoek het bestand (New.xlsx) en klik op Open.

Alle verwijzingen naar het origineel (Original.xlsx) worden verwijderd.

DONE!

• kopieer werkblad naar ‘WerkboekB.xlsx’
• open werkblad in het nieuwe bestand
• selecteer alle
• ga naar menu Gegevens, klik op bewerkingslinks
• bewerkingslinks zodat de link naar het oude bestand nu een link is naar het nu geopende bestand

Dit werkt voor mij.

Als u dit automatisch moet doen omdat u in een VBA-programma vellen binnenhaalt. Gebruik dit: 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

Wijzig gewoon “String gevonden in de naam van het bronboek” om de oude links te vervangen. Je zou dat kunnen verwijderen als je alle links wilt vervangen.

Een andere ‘truc’ - vervang vóór het kopiëren van het bronwerkblad alle formulekwalificaties = door andere tekens (bijvoorbeeld ###=).

Kopieer het werkblad over, en vervang dan na het kopiëren de formulekwalificatie terug (vervanging van ###= door =).

Zorg ervoor dat alle bladverwijzingen binnen de formauls ook naar het nieuwe blad worden gekopieerd voorafgaand aan het verwijzingsblad.

Beide werkboeken moeten openstaan om dit te laten werken. Je voert deze macro uit en het zal workbookA!sheet1 naar workbookB!sheet1 kopiëren en dan alle workbookA referenties vervangen. Het is grof, maar het werkt. U kunt uiteraard de code wijzigen zodat deze overeenkomt met de naam van uw werkboekA.xlsx, maar zorg ervoor dat ze de juiste extensie hebben en tussen aanhalingstekens blijven staan.

Oh, om een macro te maken, voor het geval u het niet weet, druk op alt + F11 om de Visual Basic Editor te laten verschijnen. Klik vervolgens met de rechtermuisknop op WBA insert - module en kopieer en plak de onderstaande code in de module. Druk vervolgens op F5 om de macro uit te voeren. Als de macro niet werkt, is dat waarschijnlijk omdat macro’s niet zijn ingeschakeld, dus sla het op en open het opnieuw en als het vraagt om macro’s in te schakelen, schakel ze dan in.

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

Ik had een soortgelijk probleem. De reden dat de formules werden geplakt met de link naar WBA was dat het tabblad (sheet) waar ik mee bezig was in WBA anders werd genoemd dan in WBB. Voor mij was het altijd “het laatste vel” maar het ene heette ‘MinFlow’ en het andere heette ‘NormalFlow’. Ik hernoemde zowel de naam ‘Results’ als de copy/paste werkte zoals ik dat wilde - een ‘relatieve pasta’.

Selecteer de cellen die u wilt verplaatsen. Probeer ze nu te verplaatsen door ze naar een ander werkblad (ander tabblad) te slepen.

Ik weet het, het rolt. Hier is het lastige gedeelte: druk gewoon op cmd (mac) of alt (win) en je kunt de cellen naar een ander tabblad slepen.

Ik heb dit bereikt door de cellen naar het nieuwe werkblad te kopiëren zoals normaal, dan een zoekopdracht te doen om het oude bestandspad in de formules te verwijderen.

Bijvoorbeeld als de eerste formule =J2 is en dit wordt =[filepath]J2, dan doe je gewoon een zoekopdracht en vervang je het hele nieuwe werkblad voor [filepath] en vervang je het door niets. Dit verwijdert het en herstelt de formule naar =J2.

Geen VB nodig!

Hallo hier is een eenvoudige oplossing voor dit probleem:

1. Kopieer over de cellen zoals gewoonlijk.
2. 2. Selecteer en kopieer in de formule de tekst die aan het vorige werkboek [WerkboekA.xlsx] is gekoppeld.
3. 3. Selecteer alle cellen die u wilt wijzigen en druk op CTRL+F en selecteer het vervangende tabblad.
4. Selecteer de cellen die u wilt wijzigen en druk op Replace with en selecteer het vervangende tabblad. 4. Vervang [WorkbookA.xlsx] door lege ruimte (aka schrijf niets in het Replace All veld, druk op &007.

Voila - het is klaar.

1. 1. Kopieer het blad zoals gewoonlijk. (rechtermuisklik op de tab en ga naar ‘verplaatsen of kopiëren’) Dit is voor opmaak.
2. 1. Kopieer alle cellen van het originele vel (met behulp van Ctrl+A of linkerbovenhoek en Ctrl+C)
3. 1. Plakken als waarden in het nieuwe werkboek (over “stap-1” vel) (Plakopties >123)

Open beide werkboeken.  Selecteer in het bronwerkboek (WorkbookA.xlsx) het blad dat u wilt kopiëren.  Klik met de rechtermuisknop op het bladtabblad en selecteer “Verplaatsen of kopiëren…”.  Selecteer in het dialoogvenster “Verplaatsen of kopiëren” “WorkbookB.xlsx” in de vervolgkeuzelijst “Naar boek”, kies waar in dat boek u het wilt plaatsen en vink “Create a copy”.&nbsp aan; (En klik op “OK”.)

Maak een kopie van een vel dat je wilt verplaatsen, waarvan dit geval WorkbookA.xlsx zou zijn. Hernoem het naar “Kopie van WorkbookA.xlsx”. Open nu dit nieuwe werkboek en het werkboek waar je het vel naartoe wilt verplaatsen, in dit geval WorkbookB.xlsx. Klik met de rechtermuisknop op de vellen van het door u gemaakte kopieerwerkblad Copy of WorkbookA.xlsx en selecteer ‘Verplaats of kopieer’ en verplaats deze vellen naar WorkbookB.xlsx. U bent klaar !