Como corrigir a parte do nome da folha de uma referência de célula do Excel quando a folha inteira é copiada?

Question

Eu quero fazer referência a outra célula na minha planilha de tal forma que quando eu copiar a folha, ainda estou apontando para a célula na folha original. Se a referência for a uma célula em uma planilha diferente, o nome da planilha será preservado quando a planilha for copiada, mas se a referência estiver na mesma planilha, o Excel alterará o nome da planilha para a planilha de destino quando a planilha for copiada. >

Eu tentei definir um nome de célula, tornando seu escopo a pasta de trabalho inteira e fazer referência à célula pelo nome, mas, quando copio a planilha, o Excel "ajuda" cria um nome duplicado com o escopo apenas da planilha de destino. / p>

Eu sei que posso usar a função "Indireta" para referenciar uma célula com uma referência que não irá mudar quando a planilha for copiada, mas eu não quero fazer isso porque ela falhará se alguma alteração estrutural for feita na folha contendo a célula.

O aplicativo requer que sejam feitas cópias sucessivas de cada folha para acompanhar os instantâneos dos dados ao longo do tempo, mas, para um aspecto, eu sempre quero me referir novamente a uma célula na planilha original. O modelo da pasta de trabalho contém apenas a planilha original, aguardando pelos dados iniciais, que serão então copiados para uma nova planilha para a próxima iteração de dados. Alguém pode me ajudar com uma técnica de referência de célula adequada para usar que não envolve um re-design do aplicativo.

    

Answer 1

Não sei se entendi completamente seus requisitos / restrições, mas sugiro isso de qualquer forma. Definir uma função VBA

Function Original(Ref)
    Application.Volatile
    With Application.Caller.Parent
        Original = .Parent.Sheets(1).Range(Ref.Address)
    End With
End Function

Substitua o 1 em Sheets(1) pelo número da planilha que você deseja referenciar. ou seu nome, por exemplo, Sheet("PeterK_Master") . Agora, você quer uma referência copiável para Sheet1!Q42 , apenas diga Original(Q42) . Isso será atualizado automaticamente se você inserir colunas à esquerda de Q ou linhas acima de 42 (isso é o que você queria, certo?). E, se você copiá-lo / arrastá-lo, ele será atualizado automaticamente como qualquer outro parente referência (mesma folha); e, claro, você pode parar usando $ s.