Insere linhas na planilha de outra pasta de trabalho?

Question

Eu tenho duas pastas de trabalho, por exemplo: workbook_a.xlsx e workbook_b.xlsx e ambas contêm planilhas diferentes. Duas dessas planilhas já estão vinculadas por algumas fórmulas VLOOKUP usando uma chave exclusiva que é a coluna custid . Com essa configuração, posso permitir que os usuários insiram dados no workbook_a e, em seguida, posso executar todo o meu BI no workbook_b . Agora, uma das coisas mais tediosas que faço é procurar novos clientes de alto risco em workbook_a para que possamos adicioná-los manualmente a workbook_b . Eu gostaria de fazer o seguinte:

1. De pasta de trabalho_a pesquisa custid em workbook_b .
2. Se existir, não faça nada (já adicionei o cliente e o A fórmula do VLOOKUP cuidará da atualização do valor da célula cust_risk ).
3. Se não existir, copie a linha de pasta de trabalho_a para workbook_b (talvez nem toda a linha, mas apenas uma célula?).

Isso é possível? Estou usando o Excel 2007.

    

Answer 1

Sim, você pode criar uma macro VBA para isso.

Eu criaria um botão e, no evento de clique, primeiro verifique se o workbook_b está aberto e use o seguinte código para copiar as linhas onde custid está em workbook_a, mas não no workbook_b (observe que no final você acabou de acabar com uma cópia workbook_a no workbook_b, então seria muito mais simples copiar todo o intervalo de dados).

Dim i As Integer, workingCol1 As Integer, workingCol2 As Integer
Dim workingRange1 As Range, workingRange2 As Range

workingCol1 = WorksheetFunction.Match("custid", Sheets("Sheet1").UsedRange.Rows(1), 0)
Set workingRange1 = Sheets("Sheet1").Range("AllCustomers").Columns(workingCol1)

workingCol2 = WorksheetFunction.Match("custid", Sheets("Sheet2").UsedRange.Rows(1), 0)
Set workingRange2 = Sheets("Sheet2").Range("CriticalCustomers").Columns(workingCol2)

For i = 2 To workingRange1.Rows.Count
   If Not IsError(Application.Match(workingRange1.Cells(i, 1), workingRange2, 0)) Then
    workingRange1.Rows(i).EntireRow.Copy
    Sheets("Sheet2").UsedRange.Rows(Sheets("Sheet2").UsedRange.Rows.Count).Offset(1, 0).EntireRow.PasteSpecial (xlPasteValues)
  End If
Next i

Se você realmente só precisa de uma célula copiada, então você poderia fazê-lo com uma função de planilha, mas seria um pouco complicado.

    

Answer 2

Se você estiver interessado, outra abordagem seria primeiro configurar um intervalo com nome dinâmico em workbook_a, que abrange a área em que você permite que os usuários insiram dados. Em seguida, use esse intervalo nomeado dinâmico como a origem de uma tabela dinâmica em workbook_b.

Todas as alterações que você fizer nos seus dados em workbook_a, incluindo novos clientes, serão selecionadas quando você atualizar a tabela dinâmica no workbook_b.

Observe que as duas pastas de trabalho precisam estar abertas.

Especificamente, quando brinquei com isso, nomeei meu intervalo dinâmico em workbook_a como "AllCustomerData", com os dados iniciando na célula A1 em sheet1 e definindo a fórmula para "AllCustomerData" como

 =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Em seguida, para a fonte da tabela dinâmica no workbook_b, usei

 workbook_a.xlsx!AllCustomerData

Em seguida, quando criei a tabela dinâmica, escolhi o "layout de Tabela Dinâmica Clássica". Coloquei todos os meus campos na seção "RowLabels" da caixa Lista de Campos da Tabela Dinâmica, na ordem dos meus campos na pasta de trabalho original_a. Você pode, é claro, encomendá-las como desejar - ou apenas selecionar o (s) campo (s) de que precisa.

Quando eu testei isso, percebi que, se eu excluísse um cliente, atualizasse a tabela dinâmica, reutilizasse a CUSTID do cliente excluído na parte inferior do intervalo de origem e, novamente, atualizasse a tabela dinâmica. CUSTID usado apareceria na tabela dinâmica não na parte inferior, mas na posição original antes da exclusão. (Eu espero que isso tenha sido claro!) Eu não sei se suas regras de negócios permitem a reutilização de CUSTIDs, mas esteja ciente disso se você fizer isso.

Espero que isso ajude. A abordagem VBA do @Lance Roberts também é muito boa. Ele tem uma vantagem sobre essa abordagem de tabela dinâmica de não incorrer em toda a "sobrecarga da tabela dinâmica".