Excel VBA - automatização de encontrar dados em uma planilha (find) e colocar em outra

isabela queiroz · 5 de setembro de 2022

preciso que essas ações sejam automatizadas através do vba:

Ir em na planilha que se chama "sheet1" pegar a primeira familia(produto), procurar na coluna familia(produto) da planilha "tamplete_peso_medida"essa é a planilha que se chama "sheet1"

essa é a planilha que se chama "tamplate_peso_medida"

depois que encontrar copiar todas as informações dela, e jogar na planilha sheet1 sem formatação.

Para localizar as colunas, preciso que seja pela opção FIND pois o número de colunas pode mudar no template

Midori · 5 de setembro de 2022

Faltou anexar o arquivo

isabela queiroz · 5 de setembro de 2022

@Midoriaqui, algumas informações não estão aparecendo pois elas vem de outras planilhas, não enviei aqui por ser da empresa

HP COMP DATA.xlsx

Midori · 5 de setembro de 2022

@isabela queiroz Isso poderia ser feito com fórmula, no caso o PROCV se a correspondência for a mesma. Mas parece que o nome da família não é exatamente igual nas tabelas. Na Sheet1 "250G8 - MALDIVES 6U" é "MALDIVES 6U 250" na outra?

isabela queiroz · 5 de setembro de 2022

@Midori basicamente isso

34 minutos atrás, isabela queiroz disse:

@Midori basicamente isso, como faria isso pelo PROCB?

isabela queiroz · 6 de setembro de 2022

19 horas atrás, isabela queiroz disse:

@Midori basicamente isso

esse comando procb é por celula ou tem como fazer usando o FIND? porque o numero de colunas podem mudar conforme o excel que recebo, precisava que fosse feito no vba.

Midori · 6 de setembro de 2022

@isabela queiroz A busca pode ser feita com FIND ou PROCV, porém é preciso ter um padrão de busca para a função encontrar o resultado.

No caso da família 'PIKE', na Sheet1 está como 'Z4G4 - PIKE' e assim a busca não vai retornar nada já que os valores não são os mesmos nas duas tabelas. Nesse caso poderiamos adaptar a fórmula para identificar o sinal em 'Z4G4 - PIKE' para pegar apenas o trecho da palavra que pode ser encontrada na outra tabela. Você teria que dar mais detalhes sobre casos assim já que além desse tem o outro que comentei anteriormente.

Sobre o número de colunas de que forma elas podem mudar? Normalmente numa tabela são as linhas que aumentam a medida que novos dados são acrescentados.

isabela queiroz · 6 de setembro de 2022

@Midori então, eu fiz assim por enquanto

' Macro1 Macro
'

'
Sheets("Sheet1").Select
ActiveSheet.Range("$A$1:$C$349").AutoFilter Field:=3, Criteria1:= _
"250G8 - MALDIVES 6U"
Range("C4").Select
ActiveCell.FormulaR1C1 = "250G8 - MALDIVES 6U"
Sheets("TEMPLATE_PESO_MEDIDA").Select
ActiveSheet.Range("$A$2:$AQ$48").AutoFilter Field:=2, Criteria1:= _
"MALDIVES 6U 250" 'procurando no tamplate a familia
Range("AD31:AQ31").Select 'copiando as info da familia
Selection.Copy
Sheets("Sheet1").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'colando as info em sheet1 sem formatação
Application.CutCopyMode = False

'----------------------------------------------------------------------------

Range("D4").Select
Range(Selection, Selection.End(xlToRight)).Select 'seleciona ate a informação da linha
Application.CutCopyMode = False 'copia a linha
Selection.Copy
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 'seleciona ate a ultima informção da tabela
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'cola sem formatação

End Sub

to indo na base do ctrl c ctrl v só da informação que necessito da familia, o problema agora ta sendo que ta copiando pra tabela inteira, não somente as informaçoes que eu trouxe

@Midori sobre o numero de colunas, acredito que tenham me solicitado assim porque as vezes as tabelas podem vir com mais colunas de informaçoes antes, não sei porque mudariam assim mas foi o que me solicitaram.

isabela queiroz · 6 de setembro de 2022

3 horas atrás, isabela queiroz disse:

@Midori então, eu fiz assim por enquanto

' Macro1 Macro
'

'
Sheets("Sheet1").Select
ActiveSheet.Range("$A$1:$C$349").AutoFilter Field:=3, Criteria1:= _
"250G8 - MALDIVES 6U"
Range("C4").Select
ActiveCell.FormulaR1C1 = "250G8 - MALDIVES 6U"
Sheets("TEMPLATE_PESO_MEDIDA").Select
ActiveSheet.Range("$A$2:$AQ$48").AutoFilter Field:=2, Criteria1:= _
"MALDIVES 6U 250" 'procurando no tamplate a familia
Range("AD31:AQ31").Select 'copiando as info da familia
Selection.Copy
Sheets("Sheet1").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'colando as info em sheet1 sem formatação
Application.CutCopyMode = False

'----------------------------------------------------------------------------

Range("D4").Select
Range(Selection, Selection.End(xlToRight)).Select 'seleciona ate a informação da linha
Application.CutCopyMode = False 'copia a linha
Selection.Copy
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 'seleciona ate a ultima informção da tabela
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'cola sem formatação

End Sub

to indo na base do ctrl c ctrl v só da informação que necessito da familia, o problema agora ta sendo que ta copiando pra tabela inteira, não somente as informaçoes que eu trouxe

@Midori sobre o numero de colunas, acredito que tenham me solicitado assim porque as vezes as tabelas podem vir com mais colunas de informaçoes antes, não sei porque mudariam assim mas foi o que me solicitaram.

bom assim ta dando quase certo:

Sub Macro1()
'
' Macro1 Macro
'

'
Sheets("Sheet1").Select
ActiveSheet.Range("$A$1:$C$349").AutoFilter Field:=3, Criteria1:= _
"250G8 - MALDIVES 6U"
Range("C4").Select
ActiveCell.FormulaR1C1 = "250G8 - MALDIVES 6U"
Sheets("TEMPLATE_PESO_MEDIDA").Select
ActiveSheet.Range("$A$2:$AQ$48").AutoFilter Field:=2, Criteria1:= _
"MALDIVES 6U 250" 'procurando no tamplate a familia
Range("AD31:AQ31").Select 'copiando as info da familia
Selection.Copy
Sheets("Sheet1").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'colando as info em sheet1 sem formatação
Application.CutCopyMode = False

'----------------------------------------------------------------------------

Range("D4").Select
Range(Selection, Selection.End(xlToRight)).Select 'seleciona ate a informação da linha
Application.CutCopyMode = False 'copia a linha
Selection.Copy

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste

mas o problema agora é que não ta localizando a celula que esta as informações, ta copiando sempre da celula "AD4" , to pensando em fazer uma InputBox que la eu procuro o dado que necessito, mas ainda preciso que seja encontrado pelo FIND e que pegue qual celula certa ta esse dado e ai copiar as informaões na frente dele.

Midori · 6 de setembro de 2022

4 horas atrás, isabela queiroz disse:

ActiveSheet.Range("$A$1:$C$349").AutoFilter Field:=3, Criteria1:= _
"250G8 - MALDIVES 6U"
Range("C4").Select
ActiveCell.FormulaR1C1 = "250G8 - MALDIVES 6U"
Sheets("TEMPLATE_PESO_MEDIDA").Select
ActiveSheet.Range("$A$2:$AQ$48").AutoFilter Field:=2, Criteria1:= _
"MALDIVES 6U 250" 'procurando no tamplate a familia

É sobre isso que comentei.

Quando você faz uma busca numa planilha, espera um padrão para conseguir retornar o resultado. E isso é feito quando o valor a ser procurado é igual ao outro da planilha onde será feita a busca.

Nesse trecho de código os valores são diferentes e você teve que colocar manualmente o filtro para '250G8 - MALDIVES 6U' e a busca para 'MALDIVES 6U 250'. Numa automatização espera-se apenas programar uma vez para o código funcionar em qualquer caso e isso não acontece aí e em outros valores como o 'PIKE' que já falei antes.

Para alguns produtos da planilha Sheet1 é questão de pegar apenas o valor após o sinal para encontrar o resultado na outra. O ideal é que o padrão do nome dos produtos nas duas planilha fossem os mesmos, mas como não são terá que considerar esses pontos no código.

isabela queiroz · 8 de setembro de 2022

Em 06/09/2022 às 15:41, Midori disse:

É sobre isso que comentei.

Quando você faz uma busca numa planilha, espera um padrão para conseguir retornar o resultado. E isso é feito quando o valor a ser procurado é igual ao outro da planilha onde será feita a busca.

Nesse trecho de código os valores são diferentes e você teve que colocar manualmente o filtro para '250G8 - MALDIVES 6U' e a busca para 'MALDIVES 6U 250'. Numa automatização espera-se apenas programar uma vez para o código funcionar em qualquer caso e isso não acontece aí e em outros valores como o 'PIKE' que já falei antes.

Para alguns produtos da planilha Sheet1 é questão de pegar apenas o valor após o sinal para encontrar o resultado na outra. O ideal é que o padrão do nome dos produtos nas duas planilha fossem os mesmos, mas como não são terá que considerar esses pontos no código.

tudo bem, vou resolver isso depois então, mas ainda é possível fazer um codigo que compreenda um loop? mesmo que fosse manualmente escrito qual a familia que é necessario.

Sub Macro1()
'
' Macro1 Macro
'

Sheets("Sheet1").Select
ActiveSheet.Range("$A$1:$C$349").AutoFilter Field:=3, Criteria1:= _
"250G8 - MALDIVES 6U"

Range("C4").Select
With Worksheets("Sheet1").AutoFilter.Range
ActiveCell.Value2 = Range("D" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
End With

'Range("C4").Select
ActiveCell.FormulaR1C1 = "250G8 - MALDIVES 6U"
Sheets("TEMPLATE_PESO_MEDIDA").Select
ActiveSheet.Range("$A$2:$AQ$48").AutoFilter Field:=2, Criteria1:= _
"MALDIVES 6U 250" 'procurando no tamplate a familia
Range("D31:AQ31").Select 'copiando as info da familia
Selection.Copy
Sheets("Sheet1").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'colando as info em sheet1 sem formatação
Application.CutCopyMode = False

'-----------------visualizando a primeira celula da coluna-----------------------------

With Worksheets("Sheet1").AutoFilter.Range
ActiveCell.Value2 = Range("D" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
End With

'------------copiando pro resto das celulas filtradas ----------------------------

Range(Selection, Selection.End(xlToRight)).Select 'seleciona ate a informação da linha
Application.CutCopyMode = False 'copia a linha
Selection.Copy


Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste

end sub

queria transformar isso num loop, mas toda vez que terminasse de preencher os dados de um, abrisse uma texbox pedindo pra inserir o nome da proxima familia pra encontrar e a textbox viesse de um botao.

Midori · 8 de setembro de 2022

1 hora atrás, isabela queiroz disse:

queria transformar isso num loop, mas toda vez que terminasse de preencher os dados de um, abrisse uma texbox pedindo pra inserir o nome da proxima familia

Na planilha Sheet1, sem contar as repetições, tem 10 produtos. Você quer que o Formulário abra 10 vezes durante o loop para filtrar e copiar os dados? Aí seriam dois TextBox, um para o nome do produto de Sheet1 e outro para TEMPLATE_PESO_MEDIDA, é isso?

isabela queiroz · 8 de setembro de 2022

7 minutos atrás, Midori disse:

Na planilha Sheet1, sem contar as repetições, tem 10 produtos. Você quer que o Formulário abra 10 vezes durante o loop para filtrar e copiar os dados? Aí seriam dois TextBox, um para o nome do produto de Sheet1 e outro para TEMPLATE_PESO_MEDIDA, é isso?

então, to tentando achar uma forma de deixar isso mais simples, textbox me parece o caminnho mais fácil, tava pensando em fazer uma tabela ao lado que fosse preenchida com as informaçoes de quais as familias que vou precisar pesquisar dentro da planilha TAMPLATE_PESO_MEDIDA, pra ai depois fazer só um textbox que eu mesma digitaria familia por familia usando a tabela que falei em criar como base pra ficar mais rápido.

2 minutos atrás, isabela queiroz disse:

então, to tentando achar uma forma de deixar isso mais simples, textbox me parece o caminnho mais fácil, tava pensando em fazer uma tabela ao lado que fosse preenchida com as informaçoes de quais as familias que vou precisar pesquisar dentro da planilha TAMPLATE_PESO_MEDIDA, pra ai depois fazer só um textbox que eu mesma digitaria familia por familia usando a tabela que falei em criar como base pra ficar mais rápido.

não sei se é possível mas a tabela seria criada baseada no filtro de "family produto" da planilha "sheet1"

Midori · 9 de setembro de 2022

21 horas atrás, isabela queiroz disse:

ava pensando em fazer uma tabela ao lado que fosse preenchida com as informaçoes de quais as familias que vou precisar pesquisar dentro da planilha TAMPLATE_PESO_MEDIDA

Pode ser assim também para a macro filtrar com base nos registros dela.

isabela queiroz · 9 de setembro de 2022

@Midori teria uma noção de como eu faria esse codigo? to tentando pensar em algo mas como tenho pouco conhecimento de vba to tendo que descobrir as coisas agora antes de pensar sobre se funciona ou não.

Midori · 12 de setembro de 2022

@isabela queiroz Crie uma nova aba (Produto) com a tabela, p.ex,

Com essa relação a macro pode procurar e filtrar na planilha Sheet1,

Sub Atualiza()
    Dim Produto As Range
    
    Set Produto = ThisWorkbook.Sheets("Produtos").[A2]
    
    While Produto <> ""
        Call CopiaProduto(Produto, Produto(1, 2))
        Set Produto = Produto(2)
    Wend
End Sub

Sub CopiaProduto(ByVal Produto As String, ByVal Template As String)
    Dim Area    As Range
    Dim Busca   As Range
    
    Set Area = ThisWorkbook.Sheets("Sheet1").[A1].CurrentRegion
    Call Area.AutoFilter(Field:=3, Criteria1:=Produto)
    Set Area = Area.Resize(Area.Rows.Count, 1)
    
    If Area.Offset(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
        Set Busca = ThisWorkbook.Sheets("TEMPLATE_PESO_MEDIDA").[B:B].Find( _
            What:=Template, LookIn:=xlValues, LookAt:=xlWhole)

        If Not Busca Is Nothing Then
            Set Area = Area.Resize( _
                Area.Rows.Count - 1, 1).Offset(1, 3).SpecialCells(xlCellTypeVisible)
            
            Busca.Resize(1, 42).Copy
            Area.PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        End If
    End If
End Sub

isabela queiroz · 12 de setembro de 2022

@Midori poderia me explicar melhor como funciona e como colocar a parte do seu codigo no meu?

Midori · 12 de setembro de 2022

@isabela queiroz Esse é o código completo, para testar é só colar no Módulo e executar a sub Atualiza.

isabela queiroz · 12 de setembro de 2022

então, eu fiz isso e ta dando erro 9 de subscript out of range e não to sabendo achar ele

Midori · 12 de setembro de 2022

@isabela queiroz Eu qual linha deu erro?

isabela queiroz · 12 de setembro de 2022

@Midori

consegui fazer parar de dar o erro mas eu to tentando fazer o codigo pegar da coluna "G" colar sem copia na coluna "A" pra depois ir para a planilha produtos e então fazer o seu codigo, porém não entendi ele muito bem.

Sub Macro1()

Columns("G:G").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-12

ActiveSheet.Range("A1:C1000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

'---------------------------------------------------------------
'Sub Atualiza()
Dim Produto1 As Range

Set Produto1 = ThisWorkbook.Sheets("Produtos").[A2]

While Produto1 <> ""
Call CopiaProduto(Produto1, Produto1(1, 2))
Set Produto1 = Produto1(2)
Wend
'End Sub

'Sub CopiaProduto(ByVal Produto As String, ByVal Template As String)
Dim Area As Range
Dim Busca As Range
Dim Produto As String 'teste
Dim Template As String


Set Area = ThisWorkbook.Sheets("Sheet1").[A1].CurrentRegion
Call Area.AutoFilter(Field:=3, Criteria1:=Produto)
Set Area = Area.Resize(Area.Rows.Count, 1)

If Area.Offset(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
Set Busca = ThisWorkbook.Sheets("TEMPLATE_PESO_MEDIDA").[B:B].Find( _
What:=Template, LookIn:=xlValues, LookAt:=xlWhole)

If Not Busca Is Nothing Then
Set Area = Area.Resize( _
Area.Rows.Count - 1, 1).Offset(1, 3).SpecialCells(xlCellTypeVisible)

Busca.Resize(1, 42).Copy
Area.PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If


End If
'End Sub

Sheets("Sheet1").Select
ActiveSheet.Range("$A$1:$C$349").AutoFilter Field:=3, Criteria1:= _
"250G8 - MALDIVES 6U"

Range("G1").Select

With Worksheets("Sheet1").AutoFilter.Range
ActiveCell.Value2 = Range("H" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
End With

'Range("C4").Select
ActiveCell.FormulaR1C1 = "250G8 - MALDIVES 6U"
Sheets("TEMPLATE_PESO_MEDIDA").Select
ActiveSheet.Range("$A$2:$AQ$48").AutoFilter Field:=2, Criteria1:= _
"MALDIVES 6U 250" 'procurando no tamplate a familia
Range("D31:AQ31").Select 'copiando as info da familia
Selection.Copy
Sheets("Sheet1").Select
Range("H4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'colando as info em sheet1 sem formatação
Application.CutCopyMode = False

'-----------------visualizando a primeira celula da coluna-----------------------------

With Worksheets("Sheet1").AutoFilter.Range
ActiveCell.Value2 = Range("H" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
End With

'------------copiando pro resto das celulas filtradas ----------------------------

Range(Selection, Selection.End(xlToRight)).Select 'seleciona ate a informação da linha
Application.CutCopyMode = False 'copia a linha
Selection.Copy


Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste

End Sub

Midori · 12 de setembro de 2022

@isabela queiroz Chegou a testar só o meu código na planilha que anexou aqui? O que quer dizer com "colar sem copia"? Não entendi o que quer fazer agora e a sua tabela de Produtos está diferente o que sugeri. Pode anexar o arquivo?

isabela queiroz · 12 de setembro de 2022

@Midori se eu coloco só seu codigo da isso aqui

tava fazendo algumas alterações no meu codigo e agora ta todo bagunçado, eu queria que o codigo começasse na planilha "sheet1" separando do lado uma tabela com todas as familiaas da coluna "G" tirando as copias no caso deixando somete as 10 familias, em seguida pegasse essas familias e colocasse em ordem igual voce fez na sua tabela com a coluna "B" do "tamplete" e em seguida fizesse a pesquisa nas planilhas, pegando da planilha sheet1 o nome "PIKE" por exemplo, pesquisando na planilha TAMPLETE_PESO_MEDIDA e em seguida colocando de volta na "sheet1" em frente a cada um, como tem o "maldives 6U" de exemplo.

não consigo enviar a planilha por ser mais pesada do que pode aqui no clube.

mas a planilha tamplete_peso_medida continua a mesma coisa só mudei de lugar a tabela do sheet1 e coloquei mais uma no canto

Midori · 12 de setembro de 2022

13 minutos atrás, isabela queiroz disse:

se eu coloco só seu codigo da isso aqui

Fiz na planilha que anexou aqui no tópico e não deu nenhum erro.

Antes de executar você tem que criar a aba Produtos e lá deve colocar esta tabela,

13 minutos atrás, isabela queiroz disse:

mas a planilha tamplete_peso_medida continua a mesma coisa só mudei de lugar a tabela do sheet1 e coloquei mais uma no canto

Nesse caso a macro que postei tem que ser modificada.

Mas acho que devia tentar rodar só o meu código sem alteração na planilha que anexou aqui e com a tabela que comentei acima. Para só depois modificar algo se necessário.