Copiar a folha de trabalho Excel e manter a referência relativa das células nas fórmulas

Em muitos casos, foi-me mais fácil fazer o seguinte:

• copiar a folha para uma nova pasta de trabalho
• activar a nova folha na nova pasta de trabalho
• seleccionar tudo (Ctrl+A)
• fazer um achado/substituição em
• achar: [WorkbookA.xlsx]!
• substituir: <deixar em branco\>
• substituir todos

Tente usar Ctrl + ~ para exibir as fórmulas. Depois use Ctrl + A para seleccionar tudo, copie e depois cole no bloco de notas.

Finalmente, copie do bloco de notas e cole no seu outro bloco de notas.

A resposta não assinada logo abaixo desta é a que funcionou para mim, com uma variação muito ligeira.

1. Criar e guardar uma folha de cálculo de destino.

2. Use “mover”, “copiar”, ou arraste a sua página com as fórmulas para a nova folha de cálculo. Isto deixa as fórmulas na nova página a apontar para a folha de cálculo antiga. Depois guarda a nova folha de cálculo no mesmo local que a folha de cálculo antiga.

3. Depois vá para o separador Dados > clique em Editar Links. A opção não estará activa a menos que existam links na página.

4. Na caixa de diálogo que resulta, seleccione o nome do ficheiro-fonte e clique em “Change Source” (Alterar Fonte).

5. Na caixa de diálogo de ficheiro aberto que aparece a seguir, seleccione o nome da nova folha de cálculo.

Clique em Close (Fechar) e está pronto.

Ou simplesmente faça o seguinte:

Converta isto:

=database_feed!A1

para isto:

=INDIRECT("database_feed!A1")

e não altere mais as suas referências quando copiar entre folhas de trabalho.

Se não tiver muitas folhas referenciadas, outra alternativa seria utilizar

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

e introduzir o nome da folha de referência na célula B1. Agora só tens uma célula para actualizar quando copiada para a nova folha de cálculo.

O código abaixo pode ser adaptado às suas necessidades. Tira todas as fórmulas da folha em wb1 e aplica-as a uma folha de uma nova pasta de trabalho. As fórmulas são aplicadas como String, pelo que não há inserção de referências à caderneta de trabalho original. Além disso, este código é super rápido porque não utiliza a prancheta e não requer nenhum laço através das células.

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

Uma vez que 99% das respostas nem sequer abordaram a questão original, eis a resposta adequada.

1. Copie as folhas do ficheiro original (Original.xlsx) para o novo ficheiro Excel (New.xlsx), como normalmente faria. Geralmente, clico com o botão direito do rato no nome e escolho “Move or Copy…”.

2. Guarde o segundo - ficheiro recentemente criado (New.xlsx).

3. No novo ficheiro, em Data, clique em “Edit Links”

4. No pop-up, escolha “Change Source…”

5. Localize o ficheiro (New.xlsx) e clique em Open.

Todas as referências ao original (Original.xlsx) serão removidas.

FEITO!

• copiar folha de trabalho para ‘WorkbookB.xlsx’
• abrir folha de trabalho no novo ficheiro
• seleccionar todos
• ir ao menu Dados, clicar em editar links
• editar links para que o link para o ficheiro antigo seja agora um link para o ficheiro actualmente aberto

Isto funciona para mim.

Se precisar de o fazer automaticamente porque está a puxar folhas num programa VBA. Use isto:

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

Basta alterar “String found in source book name” para corresponder aos links antigos que pretende substituir. Pode remover isso se quiser substituir todos os links.

Outro ‘truque’ - antes de copiar a folha de trabalho de origem, substituir todos os qualificadores de fórmula = por outro conjunto de caracteres (digamos ###=).

Copiar a folha de trabalho, depois de copiar, substituir de volta o qualificador de fórmula (substituindo ###= por =).

Certifique-se de que quaisquer referências de folhas dentro das formulas são também copiadas para a nova folha antes da folha de referência.

Para que isto funcione, ambos os livros de exercícios devem estar abertos. Execute esta macro e ela irá copiar workbookA!sheet1 para workbookB!sheet1 e depois substituir todas as referências workbookA. É grosseiro, mas funciona*. Pode obviamente alterar o código para corresponder aos nomes da sua WorkbookA.xlsx, mas certifique-se de que têm a extensão correcta e permanecem entre aspas.

Oh, para fazer uma macro, caso não saiba, carregue em alt + F11 para trazer o Editor Visual Basic. Depois clique com o botão direito do rato em WBA insert - module e copie e cole o código abaixo no módulo. De seguida carregue em F5 para executar a macro. Se a macro não funciona, provavelmente é porque as macros não estão activadas, por isso vá em frente e guarde-a e reabra-a e quando pedir para activar as macros, active-as.

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

Eu estava a ter um problema semelhante. A razão pela qual as fórmulas foram coladas com a ligação à WBA foi que o separador (folha) em que eu estava a trabalhar na WBA tinha um nome diferente do da WBB. Para mim, era sempre “a última folha”, mas uma chamava-se “MinFlow” e a outra “NormalFlow”. Renomeei ambos para “Resultados” e a cópia/colar funcionou como eu queria - uma “pasta relativa”.

Seleccione as células que pretende mover. Agora tente movê-las arrastando n drop para uma folha de trabalho diferente (separador diferente).

Eu sei, rola-se. Aqui está a parte complicada: basta premir cmd (mac) ou alt (win) e deixa-te largar as células para outra aba.

Consegui isto copiando as células para a nova folha de cálculo como de costume, depois fazendo uma procura e substituição para remover o caminho do ficheiro antigo nas fórmulas.

Por exemplo, se a primeira fórmula for =J2 e esta se tornar =[filepath]J2, então basta fazer uma procura e substituição de toda a nova folha de cálculo para [filepath] e substituir por nada. Isto apaga e restaura a fórmula para =J2.

Não é necessário VB!

Olá, aqui está uma solução simples para este problema:

1. Cópia sobre as células como de costume.
2. Na fórmula seleccione e copie o texto que o liga à pasta de trabalho anterior [WorkbookA.xlsx].
3. Seleccione todas as células que pretende alterar e prima CTRL+F e seleccione o separador de substituição.
4. Substitua [WorkbookA.xlsx] por espaço vazio (também conhecido por não escrever nada no campo Replace with, prima Replace All.

Voila - está feito.

1. Copiar a folha como habitualmente. (clique com o botão direito do rato no separador e vá para “mover ou copiar”) Isto é para formatação.

2. Copiar todas as células da folha original (utilizando Ctrl+A ou triângulo superior esquerdo e Ctrl+C)

3. Colar como Valores na nova pasta de trabalho (sobre a folha “step-1”) (Opções de Colar>123)

Abra ambos os workbooks.  Na pasta de origem (WorkbookA.xlsx), seleccione a folha que pretende copiar.  Clique com o botão direito do rato no separador da folha e seleccione “Move or Copy…”.  Na caixa de diálogo “Move or Copy”, seleccione “WorkbookB.xlsx” no menu pendente “To book”, escolha em que parte do livro pretende colocá-lo, e seleccione “Create a copy”.  (E clique em “OK”).

Faça uma cópia da folha que pretende deslocar, sendo este o caso do WorkbookA.xlsx. Renomeie-a para dizer “Cópia da WorkbookA.xlsx”. Abra agora esta nova pasta de trabalho, bem como a pasta de trabalho para a qual pretende mover a folha, que neste caso seria WorkbookB.xlsx. Clique com o botão direito do rato nas folhas da pasta de trabalho que criou, ou seja, Copy of WorkbookA.xlsx e seleccione “move or copy” e depois mova estas folhas para WorkbookB.xlsx. Está feito!