como copiar uma linha inteira para outra planilha usando critério

[Carlos Vizoni]

 Bom dia Pessoal!

 

Preciso copiar todas as linhas da Plan1 com a palavra "forro" para a Plan2 e depois apagar as respectivas linhas da Plan1. Fiz a macro abaixo que esta funcionado palavra por palavra, mas como são várias palavras (ex: gesso, mármore, granito, etc), preciso saber se é possível pré determinar as palavras na macro e o comando fazer todas de uma unica vez, sem ter que digitar palavra por palavra e executar a macro uma a uma.

 

Outra duvida é que quando a macro não encontra mais linhas com o critério selecionado, a macro retorna o erro:
"A variável do objeto ou a variável do bloco "With" não foi definida"

Como corrigir?

 

Abaixo a macro que consegui fazer:

 

Sub CopiaLinhaCritério()

 

Do While Range("A1").Select

Cells.Find(What:="forro").Activate

lin = ActiveCell.Row

Rows(lin).Select

Selection.Copy

Sheets("Plan2").Select

ActiveSheet.Paste

Rows("1:1").Select

Application.CutCopyMode = False

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Sheets("Plan1").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlUp

Range("A1").Select

Loop

    

End Sub

[Visitante]

 

... mas como são várias palavras (ex: gesso, mármore, granito, etc), preciso saber se é possível pré determinar as palavras na macro ...

É possível. Aí você precisa fornecer a lista completa, sem o etc...

Outra solução, mais prática, pois dispensa a necessidade de mexer no código em caso de alteração na lista de palavras, seria colocar as palavras do critério em uma coluna vazia, assim a lista poderá ser alterada diretamente na planilha e o código irá ler as palavras da lista. O que acha?

 

Outra duvida é que quando a macro não encontra mais linhas com o critério selecionado, a macro retorna o erro:

"A variável do objeto ou a variável do bloco "With" não foi definida"

Como corrigir?

Utilizando comandos adequados.

 

 

1. o seu código cola os registros na linha 1 da Plan2. Quer continuar assim ou quer colar na primeira linha vazia?

 

2. antes de colar novos registros na Plan2, a cada vez que rodar o código, você quer limpar aquela planilha ou quer adicionar os novos registros aos registros existentes?

 

3. quais as colunas utilizadas pelos registros, de "A" até __ ?

 

4. para facilitar disponibilize uma amostra do seu arquivo com ao menos dois exemplos de cada palavra que estará no critério.

[Carlos Vizoni]

Bom dia Osvaldo, a ideia de colocar a lista em uma coluna vazia é perfeita.

 

Pode colar o codigo na primeira linha vazia, sem problemas.

 

Quero adicionar os registros nos registros existentes na plan2.

 

As colunas utilizadas são de A até D.

 

Anexo uma amostra do arquivo com alguns registros.

[Visitante]

Carlos, só ficou pendente você disponibilizar uma amostra do seu arquivo e não uma "foto" de parte de uma planilha. Sem o arquivo, quem estiver disposto a te ajudar seria obrigado a reproduzir a sua tabela por digitação para efeitos de testes, pois na figura que você colocou não é possível efetuar testes.

 

Não esqueça de colocar também a lista com os termos que serão utilizados como critério.

 

Em uma olhada rápida na sua tabela me parece que todas (ou quase todas) as linhas serão transferidas para a Plan2 pois todas (?) obedecem ao critério que você antecipou no post #1. É isso?

Ainda, o seu código faz a pesquisa somente na coluna 'A' (MATERIAL 1). Vai continuar assim?

 

Ajudaria se você marcasse as linhas que serão transferidas, ou seja, as que atendem ao critério.

[Carlos Vizoni]

Boa tarde Osvaldo, não consegui anexar a planilha no post anterior pois estava fazendo na extensão *.xlsm. Agora deu certo.

Os arquivos possuem mais de 10 mil linhas cada, então preciso separar as linhas que contenham os critérios selecionados em qualquer uma das 4 colunas. 

Os termos podem variar pois haverá planilha que preciso separar os termos gesso, granito e mármore, outra terei que separar areia, pedra e vidro, por isso achei ótima a sua ideia de colocar as palavras para o critério em uma coluna vazia.

exemplo.xlsx

[Visitante]

 

Não esqueça de colocar também a lista com os termos que serão utilizados como critério.

 

 

Não encontrei a lista no seu arquivo. Onde colocou?

[Carlos Vizoni]

Não coloquei a lista pois os termos podem variar, haverá planilha que preciso separar os termos "gesso", "granito" e "mármore", outra terei que separar "areia", "pedra", e "vidro", por isso achei ótima a sua ideia de colocar as palavras para o critério em uma coluna vazia. 

[Visitante]

Carlos, experimente este código.

Sub TransfereRegistros() Dim k As Long, LRc As Long, LRo As Long, rngAF As Range  LRc = Cells(Rows.Count, 7).End(xlUp).Row   If LRc = 1 Then MsgBox "COLOQUE AO MENOS UM CRITÉRIO NA COLUNA G": Exit Sub   Application.ScreenUpdating = False    For k = 1 To 4     LRo = Cells(Rows.Count, 1).End(xlUp).Row     [G1] = Cells(1, k)     On Error Resume Next     Range("A1:D" & LRo).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _      Range("G1:G" & LRc), Unique:=False       Set rngAF = Range("A1:D" & LRo).SpecialCells(12)        If (rngAF.Cells.Count > 4) Then         Range("A2:D" & LRo).SpecialCells(12).Copy         Sheets("Plan2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial         Range("A2:D" & LRo).SpecialCells(12).ClearContents        End If       ActiveSheet.ShowAllData       Range("A2:D" & LRo).SpecialCells(4).Delete Shift:=xlUp       On Error GoTo 0    Next k [G1] = "critérios" Application.ScreenUpdating = TrueEnd Sub

 

obs.

1. coloque a lista de critérios na planilha que irá processar, coluna 'G' a partir de 'G2'
2. a lista de critérios não poderá conter células vazias
3. não preencha outras células da coluna 'G', somente a lista de critérios
4. o resultado será lançado na Plan2, na primeira linha vazia, na seguinte ordem:
primeiro os registros que atendem a lista de critérios na coluna 'A' da planilha que irá processar, depois na 'B', na 'C' e por último na 'D'
ex. se na lista de critérios houver 'gesso', serão lançados os registros que contém 'gesso' na coluna 'A' da planilha que irá processar, depois os que contém 'gesso' na coluna 'B', ..., até a coluna 'D'
5. o seu arquivo deverá ter uma planilha de nome 'Plan2'
6. a planilha a ser processada pelo código será sempre a planilha ativa, não importando seu nome

[Carlos Vizoni]

Ficou perfeito Osvaldo, 

 

Só mais um detalhe, algumas células possuem a "palavra" que esta na coluna critério da forma como o exemplo (material_gesso ou fornecedor-gesso-branco) entre outras varias opções, com a palavra do critério "perdida no meio de uma palavra mais composta", ai a macro não acha. É possível achar desta forma?

[Visitante]

Coloque o caracter coringa (*) antes e depois das palavras do critério. Exs. *gesso*, *vidro*.

 

Se quiser que o Excel coloque os caracteres pra você, instale o código abaixo e então digite por ex. gesso que o código colocará *gesso*.

 

Instale o código abaixo no módulo da planilha, assim:
1. copie o código daqui
2. clique com o direito na guia da planilha de interesse e escolha 'Exibir código'
3. cole o código na janela em branco que vai se abrir
4. feito! 'Alt+Q' para retornar para a planilha e testar

 

Private Sub Worksheet_Change(ByVal Target As Range)  If Not Application.Intersect(Target, Range("G2:G50")) Is Nothing And Target.Value <> "" Then   On Error GoTo jmp   Application.EnableEvents = False    Target.Value = "*" & Target.Value & "*"  End Ifjmp: Application.EnableEvents = TrueEnd Sub

[Carlos Vizoni]

Ficou perfeito Osvaldo, obrigado!!!