Copiar la hoja de trabajo de Excel y mantener la referencia relativa a la celda en las fórmulas

He encontrado más fácil, en muchos casos, hacer lo siguiente:

• copiar la hoja en un nuevo libro de trabajo
• activar la nueva hoja en el nuevo libro de trabajo
• seleccionar todo (Ctrl+A)
• hacer una búsqueda/reemplazo en
• encontrar: [WorkbookA.xlsx]!
• reemplazar: <Dejar en blanco…
• Reemplazar todo.

Intenta usar Ctrl + ~ para mostrar las fórmulas. Luego use Ctrl + A para seleccionar todo, cópielo y péguelo en el cuaderno.

Finalmente, cópielo del cuaderno y péguelo en su otro libro de trabajo.

La respuesta sin firmar justo debajo de esta es la que me funcionó, con una muy ligera variación.

1. Crear y guardar una hoja de cálculo de destino.

2. Usa “mover”, “copiar”, o arrastra tu página con las fórmulas a la nueva hoja de cálculo. Esto deja las fórmulas de la nueva página apuntando a la hoja de cálculo antigua. Luego guarda la nueva hoja de cálculo en la misma ubicación que la antigua.

3. Luego ve a la pestaña de datos > haz clic en Editar enlaces. La opción no estará activa a menos que haya enlaces en la página.

4. En el cuadro de diálogo que aparece, selecciona el nombre del archivo fuente y haz clic en “Cambiar fuente”.

5. En el cuadro de diálogo “Abrir archivo” que aparece a continuación, selecciona el nombre de la nueva hoja de cálculo.

Haz clic en Cerrar y habrás terminado.

O simplemente haz lo siguiente:

Convierte esto:

=database_feed!A1

a esto:

=INDIRECT("database_feed!A1")

y no más cambios en tus referencias cuando copies entre hojas de trabajo.

Si no tienes muchas hojas referenciadas, otra alternativa sería usar

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

e introducir el nombre de la hoja de referencia en la celda B1. Ahora sólo tienes una celda para actualizar cuando se copie a la nueva hoja de cálculo.

El código de abajo puede ser adaptado a sus necesidades. Toma todas las fórmulas de la hoja de wb1 y las aplica a una hoja de un nuevo libro de trabajo. Las fórmulas se aplican como Strings, por lo que no hay inserción de referencias al libro de trabajo original. Además, este código es súper rápido porque no utiliza el portapapeles y no requiere ningún bucle a través de las celdas.

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

Ya que el 99% de las respuestas ni siquiera se referían a la pregunta original, aquí está la respuesta adecuada.

1. Copie las hojas del archivo original (Original.xlsx) al nuevo archivo de Excel (New.xlsx) como lo haría normalmente. Generalmente, hago clic con el botón derecho del ratón en el nombre y elijo “Mover o Copiar…”

2. Guardar el segundo - archivo recién creado (New.xlsx).

3. En el nuevo archivo, en Datos, haga clic en “Editar enlaces”

4. En la ventana emergente, elija “Cambiar fuente…”

5. Localice el archivo (Nuevo.xlsx) y haga clic en Abrir.

Se eliminarán todas las referencias al original (Original.xlsx).

HECHO!

• copiar la hoja de trabajo a ‘WorkbookB.xlsx’
• abrir la hoja de trabajo en el nuevo archivo
• seleccionar todo
• ir al menú Datos, hacer clic en editar enlaces
• editar enlaces para que el enlace al antiguo archivo sea ahora un enlace al archivo actualmente abierto

Esto funciona para mí.

Si necesitas hacer esto automáticamente porque estás tirando de las hojas en un programa de VBA. Usa esto:

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

Sólo cambia “Cadena encontrada en el nombre del libro fuente” para que coincida con los antiguos enlaces que quieres reemplazar. Podrías quitarlo si bloqueas si quieres reemplazar todos los enlaces.

Otro “truco” - antes de copiar la hoja de trabajo de origen, reemplace todos los calificadores de fórmula = por otro conjunto de caracteres (digamos ###=).

Vuelva a copiar la hoja de trabajo, y después de la copia, reemplace el calificador de fórmula de nuevo (reemplazando ###= por =).

Asegúrese de que cualquier hoja de referencia dentro de los formularios también se copie en la nueva hoja antes de la hoja de referencia.

Ambos libros de trabajo deben estar abiertos para que esto funcione. Ejecuta esta macro y copiará workbookA!sheet1 a workbookB!sheet1 y luego reemplazará todas las referencias a workbookA. Es crudo, pero funciona. Obviamente puedes cambiar el código para que coincida con tus nombres de WorkbookA.xlsx, pero asegúrate de que tengan la extensión correcta y que permanezcan entre comillas.

Oh, para hacer una macro, en caso de que no lo sepas, pulsa alt + F11 para que aparezca el Editor de Visual Basic. Luego haga clic derecho en WBA insert - module y copie y pegue el código de abajo en el módulo. Luego pulsa F5 para ejecutar la macro. Si la macro no se ejecuta, es probablemente porque las macros no están habilitadas, así que adelante y guárdala y vuelve a abrirla y cuando te pida que habilites las macros, habilítalas.

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

Yo tenía un problema similar. La razón por la que se pegaron las fórmulas con el enlace a WBA fue que la pestaña (hoja) en la que trabajaba en WBA tenía un nombre diferente al de WBB. Para mí, siempre era “la última hoja” pero una se llamaba ‘MinFlow’ y la otra se llamaba ‘NormalFlow’. Cambié el nombre de ambas a “Resultados” y el copiar/pegar funcionó como yo quería - un “pegado relativo”.

Selecciona las celdas que quieres mover. Ahora trata de moverlas arrastrando y soltando a una hoja de trabajo diferente (diferente pestaña).

Ya sé, se desplaza. Aquí está la parte difícil: sólo tienes que pulsar cmd (mac) o alt (win) y te permitirá soltar las celdas a otra pestaña.

Lo conseguí copiando las celdas en la nueva hoja de cálculo como de costumbre, y luego haciendo un hallazgo y reemplazo para eliminar la antigua ruta de archivo en las fórmulas.

Por ejemplo, si la primera fórmula es =J2 y ésta se convierte en =[filepath]J2, entonces sólo hay que hacer un hallazgo y reemplazo de toda la nueva hoja de cálculo para [filepath] y reemplazarla por nada. Esto lo borra y restaura la fórmula a =J2.

¡No se requiere VB!

Aquí hay una solución simple a este problema:

1. Copiar sobre las celdas como de costumbre.
2. En la fórmula selecciona y copia el texto que lo vincula al libro de trabajo anterior [Libro de trabajo A.xlsx].
3. 3. Selecciona todas las celdas que quieras cambiar y pulsa CTRL+F y selecciona la pestaña reemplazar. 4. Reemplaza [Libro de trabajoA.xlsx] por un espacio vacío (es decir, no escribas nada en el campo Replace with, pulsa Replace All.

Voilà - ya está hecho.

1. Copia la hoja como de costumbre. (click derecho en la pestaña e ir a “mover o copiar”) Esto es para el formato.

2. Copiar todas las celdas de la hoja original (usando Ctrl+A o el triángulo superior izquierdo y Ctrl+C)

3. Pegar como valores en el nuevo libro de trabajo (sobre la hoja “paso 1”) (Opciones de Pegado>123)

Abra ambos libros de trabajo.  En el libro de trabajo de origen (Libro de trabajoA.xlsx), seleccione la hoja que desea copiar.  Haga clic con el botón derecho del ratón en la pestaña de la hoja y seleccione “Mover o copiar…”.  En el cuadro de diálogo “Mover o copiar”, seleccione “Libro de trabajoB.xlsx” en el menú desplegable “Para el libro”, elija en qué parte de ese libro desea colocarlo y marque “Crear a copiar”.  (Y haga clic en “Aceptar”.)

Haga una copia de la hoja que quiere mover las hojas, de la cual este caso sería WorkbookA.xlsx. Renombrarla para que diga “Copia del Libro de Trabajo A.xlsx”. Ahora abre este nuevo libro de trabajo así como el libro de trabajo al que quieres mover la hoja a la que en este caso sería Libro de TrabajoB.xlsx. Haz clic con el botón derecho del ratón en las hojas del libro de trabajo que has creado, es decir, Copia de Libro de TrabajoA.xlsx y selecciona “mover o copiar” y luego mueve estas hojas a Libro de TrabajoB.xlsx. Ya está listo!