Copiare il foglio di lavoro di Excel e mantenere il riferimento relativo alle celle nelle formule

Ho trovato più facile, in molti casi, fare quanto segue:

• copiare il foglio in una nuova cartella di lavoro
• attivare il nuovo foglio nella nuova cartella di lavoro
• selezionare tutti (Ctrl+A)
• fare una ricerca/sostituzione su
• trovare: [WorkbookA.xlsx]!
• sostituire: \“lascia vuoto \”>
• sostituisci tutti

Provare ad usare Ctrl + ~ per visualizzare le formule. Poi usate Ctrl + A per selezionare tutto, copiatelo e poi incollatelo nel blocco note.

Infine, copiatelo dal blocco note e incollatelo nell'altra vostra cartella di lavoro.

La risposta non firmata proprio sotto questa è quella che ha funzionato per me, con una leggerissima variazione.

1. 1. Creare e salvare un foglio di calcolo di destinazione.
2. 1. Usare “sposta”, “copia”, o trascinare la pagina con le formule nel nuovo foglio di calcolo. In questo modo le formule della nuova pagina puntano al vecchio foglio di lavoro. Quindi salvare il nuovo foglio di calcolo nella stessa posizione del vecchio foglio di lavoro.
3. 1. Poi andare alla scheda Dati > cliccare su Modifica collegamenti. L'opzione non sarà attiva se non ci sono collegamenti nella pagina.

4. Nella finestra di dialogo che ne risulta, selezionare il nome del file sorgente e fare clic su “Cambia sorgente”.

5. Dalla finestra di dialogo del file aperto che appare dopo, selezionare il nome del nuovo foglio di lavoro.

Fare clic su Chiudi e il gioco è fatto.

O semplicemente fare quanto segue:

Convertire questo:

=database_feed!A1

a questo:

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

e non apportare più modifiche ai vostri riferimenti quando copiate tra i fogli di lavoro.

Se non avete molti fogli di riferimento, un'altra alternativa sarebbe quella di utilizzare

&001 &001

e inserire il nome del foglio di riferimento nella cella B1. Ora si ha solo una cella da aggiornare quando viene copiato il nuovo foglio di lavoro.

Il codice sottostante può essere adattato alle vostre esigenze. Prende tutte le formule dal foglio su wb1 e le applica ad un foglio in una nuova cartella di lavoro. Le formule sono applicate come String, quindi non vi è alcun inserimento di riferimenti alla cartella di lavoro originale. Inoltre, questo codice è super veloce perché non utilizza gli appunti e non richiede alcun looping attraverso le celle.

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

Poiché il 99% delle risposte non ha nemmeno affrontato la domanda originale, ecco la risposta corretta.

1. 1. Copiate i fogli dal file originale (Original.xlsx) al nuovo file Excel (New.xlsx) come fareste normalmente. In genere, faccio clic con il tasto destro del mouse sul nome e scelgo “Sposta o Copia…”.
2. 1. Salvare il secondo - file appena creato (New.xlsx).
3. 1. Nel nuovo file, sotto Dati, fare clic su “Modifica collegamenti”

4. Nel pop-up, scegliere “Cambia origine…”

5. Individuare il file (New.xlsx) e fare clic su Apri.

Tutti i riferimenti all'originale (Original.xlsx) saranno rimossi.

DONE!

• copiare il foglio di lavoro in ‘WorkbookB.xlsx’
• aprire il foglio di lavoro nel nuovo file
• selezionare tutti
• andare al menu Dati, cliccare su modifica link
• modificare i link in modo che il link al vecchio file sia ora un link al file attualmente aperto

Questo funziona per me.

Se è necessario farlo automaticamente perché si tirano i fogli in un programma VBA. Usate questo:

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
``` &001 


Basta cambiare "Stringa trovata nel nome del libro di origine" per far corrispondere i vecchi link che volete sostituire. Potreste rimuoverlo se volete sostituire tutti i link.

Un altro ‘trucco’ - prima di copiare il foglio di lavoro sorgente, sostituire tutti i qualificatori di formula = con altri set di caratteri (ad esempio ###=).

Copiare il foglio di lavoro sopra, poi dopo la copia, sostituire il qualificatore di formula indietro (sostituendo ###= con =).

Assicurarsi che tutti i riferimenti dei fogli all'interno dei moduli siano anche copiati nel nuovo foglio prima del foglio di riferimento.

Entrambi i libri di lavoro devono essere aperti per poter funzionare. Eseguite questa macro e copiate workbookA!sheet1 in workbookB!sheet1 e poi sostituite tutti i riferimenti workbookA. È grezza, ma funziona. Potete ovviamente cambiare il codice per far corrispondere i vostri nomi WorkbookA.xlsx, ma assicuratevi che abbiano l'estensione corretta e rimangano tra virgolette.

Oh, per fare una macro, nel caso non lo sappiate, premete alt + F11 per far apparire il Visual Basic Editor. Poi cliccate con il tasto destro del mouse su WBA insert - module e copiate e incollate il codice sottostante nel modulo. Poi premere F5 per eseguire la macro. Se la macro non viene eseguita, probabilmente è perché le macro non sono abilitate, quindi salvatela e riapritela e quando vi chiede di abilitare le macro, abilitatele.

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

Avevo un problema simile. La ragione per cui le formule sono state incollate con il link a WBA era che la scheda (foglio) su cui stavo lavorando in WBA era denominata in modo diverso da quella di WBB. Per me, era sempre “l'ultimo foglio”, ma uno si chiamava ‘MinFlow’ e l'altro si chiamava ‘NormalFlow’. Ho cambiato il nome in ‘Risultati’ e il copia/incolla ha funzionato come volevo io - un “relativo incolla”.

Selezionare le celle che si desidera spostare. Ora provate a spostarle trascinando n drop su un foglio di lavoro diverso (scheda diversa).

Lo so, scorre. Ecco la parte complicata: basta premere cmd (mac) o alt (win) per far cadere le celle in un'altra scheda.

Ho ottenuto questo risultato copiando le celle nel nuovo foglio di lavoro come al solito, poi facendo un find a replace per rimuovere il vecchio percorso del file nelle formule.

Ad esempio, se la prima formula è =J2 e questa diventa =[filepath]J2, allora basta fare un find a replace dell'intero nuovo foglio di lavoro per [filepath] e sostituirlo con niente. Questo lo elimina e ripristina la formula a =J2.

Non è necessario alcun VB!

Ciao ecco una semplice soluzione a questo problema:

1. 1. Copiare sopra le celle come al solito.
2. 2. Nella formula selezionare e copiare il testo che la collega alla cartella di lavoro precedente [Cartella di lavoroA.xlsx]. 3. Selezionare tutte le celle che si desidera modificare e premere CTRL+F e selezionare la scheda replace.
3. 4. Sostituire [WorkbookA.xlsx] con uno spazio vuoto (ovvero non scrivere nulla nel campo Replace with, premere Replace All.

Voilà - è fatto.

1. 1. Copiare il foglio come al solito. (cliccare con il tasto destro del mouse sulla scheda e andare a ‘sposta o copia’) Questo è per la formattazione.
2. 1. Copiare tutte le celle dal foglio originale (usando Ctrl+A o triangolo in alto a sinistra e Ctrl+C)

3. Copiare tutte le celle dal foglio originale (usando Ctrl+A o triangolo in alto a sinistra e Ctrl+C). 3. Incollare come valori nella nuova cartella di lavoro (sul foglio “passo-1”) (Opzioni d'incollaggio>123)

Aprire entrambi i workbook.  Nella cartella di lavoro di origine (WorkbookA.xlsx), selezionare il foglio che si desidera copiare.  Fare clic con il tasto destro del mouse sulla scheda del foglio e selezionare “Sposta o Copia…”.  Nella finestra di dialogo “Sposta o Copia”, selezionare “WorkbookB.xlsx” nel menu a tendina “Per prenotare”, scegliere dove si desidera inserire il foglio, e selezionare “Crea a copia”.  (E fare clic su “OK”).

Fare una copia del foglio che si desidera spostare fogli da cui è questo caso sarebbe WorkbookA.xlsx. Rinominarlo con il nome “Copia di WorkbookA.xlsx”. Ora aprite questa nuova cartella di lavoro così come la cartella di lavoro che volete spostare il foglio in cui in questo caso sarebbe WorkbookB.xlsx. Cliccare con il tasto destro del mouse sui fogli della cartella di lavoro di copia che avete creato, cioè Copia di WorkbookA.xlsx e selezionare “sposta o copia” e poi spostare questi fogli in WorkbookB.xlsx. Avete finito!