Como posso fazer com que o produto deste snippet do VBA atualize dinamicamente a tabela?

Question

Eu tenho uma planilha mestre com todos os meus dados. Eu respeitosamente "roubei" o seguinte código para copiar todas as linhas que continham uma certa string de texto na segunda coluna para outra.

For Each r In Src.Range("B2:B" & LastRow)
 If r.Value = "1. Early Season Visits" Then
 If CopyRange Is Nothing Then
            Set CopyRange = r.EntireRow
    Else
            Set CopyRange = Union(CopyRange, r.EntireRow)
    End If
 End If
Next r
If Not CopyRange Is Nothing Then
CopyRange.Copy Dst.Range("A1")
End If
End Sub

A formatação acima foi confusa, mas você deve ter a ideia. Isso funcionou como anunciado depois que eu fiz ajustes para o meu caso particular. Ele até tentou passar sobre as regras de formatação condicional - embora eu tenha que fazer pequenos ajustes para que todos acertassem, porque o Excel fazia algum absurdo relativo de referência.

Os problemas com o acima são:

1. Os resultados são estáticos. Se eu fizer uma alteração na Folha Mestre, elas não serão atualizadas na planilha onde esse código é executado. Há uma maneira melhor / diferente de fazer isso que é atualizada em todas as planilhas.
2. Espero que, se eu atualizar manualmente executando o código novamente, a formatação condicional seja modificada novamente - o que não é uma correção insignificante.

Existe uma maneira melhor de fazer o que eu pretendo realizar? Se sim, o que é isso?

    

Answer 1

Você pode inserir fórmulas em cada célula em vez de valores

For Each r In Src.Range("A2:A" & LastRow)
  If r.Value <> "9999999**" Then
      Dst.Range("A" & r.row).Formula = "=Sheet6!A" & r.row
      Dst.Range("B" & r.row).Formula = "=Sheet6!B" & r.row
  ...and so on...

  End If
Next r

Em resposta a sua pergunta, presumo que você saiba onde colocar o código e o problema é o que ele faz.

Dst.Range("A" & r.row).Formula = "=Sheet6!A" & r.row

À medida que r percorre todas as células na Folha6, coluna A, ele pegará a linha da corrente r ( r.row ) e inserirá a fórmula na célula correspondente da Planilha7. Portanto, se r for a primeira célula na Planilha6 (A1), a célula A1 da Planilha7 terá a seguinte fórmula inserida: =Sheet6!A1 e Planilha B1 terão =Sheet6!B1 ... e assim por diante ...

Às vezes, é útil tentar inserir as fórmulas à mão primeiro para ter uma ideia melhor do que elas devem ser antes de codificá-las em sua macro