Função SE com Condição e Looping VBA

[Ana Cristina Colisse]

Boa Tarde Galera!

 

Há um tempo atrás, criei uma função SE com condição e looping no excel, e não me lembro como fiz

Preciso do seguinte:

Tenho um sheet chamado GERAL que muda de "status" na coluna Y, queria uma função que, quando o valor de determinada célula dessa coluna for "Fechado", copiasse automaticamente pro outro sheet "FECHADOS", todas as informações da linha que contém essa condição. Então seria "uma fórmula" SE valor Fechados copia, se não próxima linha... Deu pra entender? =/

[Visitante]

Sub CopiaCola()  Dim LRo As Long, LRd As Long, k As Long  Dim WSo As Worksheet, WSd As Worksheet    Set WSo = Sheets("GERAL"): Set WSd = Sheets("FECHADOS")      With WSo        LRo = .Cells(Rows.Count, "Y").End(xlUp).Row          For k = 1 To LRo            If .Cells(k, "Y") = "Fechado" Then              LRd = WSd.Cells(Rows.Count, "A").End(xlUp).Row              WSd.Cells(LRd + 1, "A").Resize(, 26).Value = .Cells(k, "A").Resize(, 26).Value            End If          Next k      End WithEnd Sub

 

[Xflapc2]

Não testei o código acima, mas você vai precisar fazer o seguinte:

 

Criar um código DENTRO da planilha específica que voce está trabalhando e nao na pasta de trabalho. "GERAL"

 

Ai você vai precisar criar o evento de quando o valor da célula mudar para valor "Fechado" aí rodar esse codigo acima.

 

Supondo que as células a serem monitoradas sejam B2 a B100:

 

Private Sub Worksheet_Change(ByVal Target As Range)Dim KeyCells As RangeSet KeyCells = Range("B2:B100")If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then  Dim LRo As Long, LRd As Long, k As Long  Dim WSo As Worksheet, WSd As Worksheet    Set WSo = Sheets("GERAL"): Set WSd = Sheets("FECHADOS")      With WSo        LRo = .Cells(Rows.Count, "Y").End(xlUp).Row          For k = 1 To LRo            If .Cells(k, "Y") = "Fechado" Then              LRd = WSd.Cells(Rows.Count, "A").End(xlUp).Row              WSd.Cells(LRd + 1, "A").Resize(, 26).Value = .Cells(k, "A").Resize(, 26).Value            End If          Next k      End WithEnd IfEnd Sub

[Ana Cristina Colisse]

Primeiramente obrigada pela atenção osvaldo e xflapc...

Decidi usar a solução do x... Alterei o Range para a coluna que preciso monitorar (obs já contem informações de Fechado nessa coluna), mas quando dou executar, nada acontece =/... Não briguem! Eu não era lerda assim... Rsrsrsrsrs enfim...

Estou mandando um print da tela onde inseri o comando para vocês verem se estou colocando as coisas em seus devidos lugares... Novamente agradeço a atenção de todos!

 

 

[Xflapc2]

Como disse o código não pode ficar em "Esta pasta de trabalho", Tem que ficar na plan que vai ser monitorada a range "geral" por exemplo.

O código do colega está ótimo, mas ele cola todas fechadas toda vez, então ficam várias cópias.

Sugiro o seguinte:

 

 - Quando mudar o status de qualquer e para qualquer situacao, ele apaga a sheet "Fechados" toda e cola tudo que estiver "Fechado" nela.

 

Lembrete: a coluna A deve estar sempre preenchida senao ela nao vai ser passada para "Fechadas".

 

Private Sub Worksheet_Change(ByVal Target As Range)Dim KeyCells As RangeSet KeyCells = Range("Y4:Y1000")If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then  Dim LRo As Long, LRd As Long, k As Long  Dim WSo As Worksheet, WSd As Worksheet    Set WSo = Sheets("GERAL"): Set WSd = Sheets("FECHADOS")     WSd.Cells.ClearContents         With WSo        LRo = .Cells(Rows.Count, "Y").End(xlUp).Row          For k = 1 To LRo            If .Cells(k, "Y") = "Fechado" Then              LRd = WSd.Cells(Rows.Count, "A").End(xlUp).Row              WSd.Cells(LRd + 1, "A").Resize(, 26).Value = .Cells(k, "A").Resize(, 26).Value            End If          Next k      End WithEnd IfEnd Sub

[Visitante]

@Ana Cristina

 

1. Se você quer de tempos em tempos fazer uma lista atualizada dos registros que foram "Fechados" então instale o primeiro código que postei, limpe a planilha "FECHADOS"  e rode o código.
Pra instalar o código:
1. copie o código daqui
2. a partir da planilha em que estão os dados tecle 'Alt+F11' para acessar o editor de VBA
3. no menu do editor >> Inserir >> Módulo
4. cole o código na janela em branco que vai se abrir
5. feito! 'Alt+Q' para retornar para a planilha e testar

para rodar o código:
6. tecle 'Alt+F8' >> selecione a macro correspondente >> Executar, ou insira um botão na planilha e vincule-o à macro ou vincule-a a um atalho de teclado (Alt+F8 > Opções).


2. Se você quiser copiar/colar o registro sempre que for digitado "Fechado" na coluna "Y" então não é necesário "Loop". Instale o código abaixo:
Instale o código abaixo no módulo da planilha "GERAL", assim:
1. copie o código daqui
2. clique com o direito na guia da planilha "GERAL" 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)  Dim LR As Long    If Target.Count > 1 Then Exit Sub    If Target.Column <> 25 Or Target.Value <> "Fechado" Then Exit Sub      LR = Sheets("FECHADOS").Cells(Rows.Count, 1).End(xlUp).Row      Sheets("FECHADOS").Cells(LR + 1, "A").Resize(, 26).Value = Cells(Target.Row, "A").Resize(, 26).ValueEnd Sub

obs. este código será disparado ao digitar "Fechado" na coluna "Y", no entanto, se "Fechado" não for digitado e sim resultado de fórmula então será necessário outro código.

 

[Ana Cristina Colisse]

Ex.xlsxBom Dia Galera

 

Seguinte, estou anexando a planilha para vocês entenderem melhor o que estou tentando fazer, no sheet Fechados, as colunas q estão com * no final do nome, são as informações que preciso que sejam preenchidas automaticamente, o resto desse sheet é preenchido por mim mesma... no sheet Geral, algumas infos são puxadas do sheet Clientes, outras preenchidas manualmente também...

Como o osvaldo citou, minha necessidade é que essas informações sejam copiadas depois de escrever Fechado, e não gerar "um relatório" de tempos em tempos, já q vou inserir outras infos no sheet Fechados.

[Visitante]

Instale o código abaixo no módulo da planilha "Geral", conforme a sequência do post #6. Remova os demais códigos daquele módulo, se houver.
 

Private Sub Worksheet_Change(ByVal Target As Range)  Dim LR As Long, k As Long, rng As Variant    If Target.Count > 1 Then Exit Sub      If Target.Column = 24 And Target.Value = "Fechado" Then        k = Target.Row            LR = Sheets("Fechados").Cells(Rows.Count, 1).End(xlUp).Row            If LR < 4 Then LR = 4            With Me              rng = Array(.Cells(k, "AC"), .Cells(k, "Y"), .Cells(k, "Z"), .Cells(k, "AA"), _                .Cells(k, "AB"), .Cells(k, "C"), .Cells(k, "D"), .Cells(k, "E"), .Cells(k, "G"), _                  .Cells(k, "H"), .Cells(k, "I"), .Cells(k, "J"), .Cells(k, "U"), .Cells(k, "W"))              Sheets("Fechados").Cells(LR + 1, 1).Resize(, 14) = rng            End With      End IfEnd Sub

funcionamento - ao inserir "Fechado" na coluna "X" da planilha "Geral" o código colará os dados da linha correspondente na planilha "Fechados".
 

[Ana Cristina Colisse]

Bom Dia!

 

Osvaldo,

 

Novamente agradecendo a atenção!

Entendi o código, inseri um módulo na planilha Geral como você especificou, segui o passo a passo certinho... Porém na hora da execução está aparecendo a seguinte mensagem: O argumento não é opcional

Fiz testes inserindo outros clientes e colocando como fechado, nada acontece... A planilha tem de estar salva com a extensão .xlsm devido ao código certo?

[Visitante]

Entendi o código, inseri um módulo na planilha Geral como você especificou, segui o passo a passo certinho...

O módulo de planilha já é existente, não precisa ser inserido. É acessado diretamente.

Só para confirmar, a sequência (é a segunda parte do post #6) para instalar o código no módulo da planilha está repetida abaixo.

 

Porém na hora da execução está aparecendo a seguinte mensagem: O argumento não é opcional

O que exatamente você fez para executar o código?

Na caixa com a mensagem há um botão "Depurar"? Se sim, ao clicar nesse botão, qual a linha do código que é destacada em amarelo?

 

A planilha tem de estar salva com a extensão .xlsm devido ao código certo?

O código deverá funcionar de imediato, assim que for instalado, sem depender de salvar o arquivo.

Ao salvar o arquivo, sim, deverá escolher a opção "habilitado para macros", (.xlsm).

 

Instale o código no módulo da planilha "GERAL", assim:

1. copie o código daqui

2. clique com o direito na guia da planilha "GERAL" e escolha 'Exibir código'

3. cole o código na janela em branco que vai se abrir (a janela que vai se abrir já é o módulo da planilha, não precisa inserir nada, é só colar o código nessa janela)

4. feito! 'Alt+Q' para retornar para a planilha e testar

 

Se não conseguir fazer funcionar sugiro que você disponibilize uma amostra do seu arquivo com o código instalado.

[Ana Cristina Colisse]

Bom Dia!

 

Osvaldo, eu não sei como... Td q você me disse eu fiz e não rodava, em outro dia fui dar uma olhada e do nada os resultados apareceram O.o

Enfim, era exatamente o que eu queria mesmo, agora só estou acertando uns detalhes... Só para entender, no intervalo:

 

 rng = Array(.Cells(k, "AC"), .Cells(k, "Y"), .Cells(k, "Z"), .Cells(k, "AA"), _

                .Cells(k, "AB"), .Cells(k, "C"), .Cells(k, "D"), .Cells(k, "E"), .Cells(k, "G"), _
                  .Cells(k, "H"), .Cells(k, "I"), .Cells(k, "J"), .Cells(k, "U"), .Cells(k, "W"))

 

você colocou as células que estão recebendo os dados, copiando os dados ou os dois?

 

Vou usar o mesmo código em outras planilhas, e não entendi muito bem essa parte do código.

 

Muito obrigada mesmo pela ajuda!

[Visitante]

Olá, Ana Cristina.

 

1. Os conteúdos das células que formam a Matriz abaixo (Array) são os dados de origem, ou seja, são dados que estão na planilha "GERAL".

Segue comentário sobre as expressões Cells(k, **), caso você queira utilizá-las em outro código:

nessas expressões "k" é uma variável que assumirá o número da linha da coluna "X" na qual for inserido "Fechado".
Ex. se for inserido "Fechado" em X10, a variável "k" terá valor 10, então Cells(k,"AC") ficaria Cells(10, "AC") que corresponde à célula "AC10".

rng = Array(.Cells(k, "AC"), .Cells(k, "Y"), .Cells(k, "Z"), .Cells(k, "AA"), _                .Cells(k, "AB"), .Cells(k, "C"), .Cells(k, "D"), .Cells(k, "E"), .Cells(k, "G"), _                  .Cells(k, "H"), .Cells(k, "I"), .Cells(k, "J"), .Cells(k, "U"), .Cells(k, "W"))

2. O comando abaixo cola os dados da Matriz acima na planilha destino, que é a planilha "Fechados".

Sheets("Fechados").Cells(LR + 1, 1).Resize(, 14) = rng