Copy rows from a table that match a criterion and paste into another Excel sheet

Question:

Hi! I'm something new to VBA and have gotten what I want through the macro recorder. However, I think that in this case I will not get what I want through this resource. I will try to be clear on my objective.

My Excel sheet is called "A3" and on that sheet I have a table located between P10:AA25 . The data is in the lines between P13:AA25 and in column Z I have the words OPEN/CLOSED . I wanted to create a macro to copy and clear the lines DA TABELA that have the criterion "CLOSED" and paste them in a table located between A1:L16 in the sheet "AÇÕES PDCA FECHADAS" .

Thanks in advance for all the help!

Answer:

As you didn't give an example of the code, I built this one with what you went through. Take the tests or check the logic here and try to apply it to your case.

Insert the following code into the spreadsheet to have the values ​​analyzed and separated:

Private Sub Worksheet_Deactivate()

Worksheets("AÇÕES PDCA FECHADAS").Range("A1:L16").ClearContents

 For I = 13 To 25
   If Worksheets("A3").Cells(I, 26) = "OPEN" Then Exit For
   If Worksheets("A3").Cells(I, 26) = "CLOSED" Then
    Linha = Application.WorksheetFunction.CountA(Worksheets("AÇÕES PDCA FECHADAS").Range("A1:L16"))
    Linha = Linha + 2
    Worksheets("AÇÕES PDCA FECHADAS").Cells(Linha, 1) = Worksheets("base").Cells(I, 16)
    Worksheets("AÇÕES PDCA FECHADAS").Cells(Linha, 2) = Worksheets("base").Cells(I, 17)
    Worksheets("AÇÕES PDCA FECHADAS").Cells(Linha, 3) = Worksheets("base").Cells(I, 18)
    Worksheets("AÇÕES PDCA FECHADAS").Cells(Linha, 4) = Worksheets("base").Cells(I, 19)
    Worksheets("AÇÕES PDCA FECHADAS").Cells(Linha, 5) = Worksheets("base").Cells(I, 20)
    Worksheets("AÇÕES PDCA FECHADAS").Cells(Linha, 6) = Worksheets("base").Cells(I, 21)
    Worksheets("AÇÕES PDCA FECHADAS").Cells(Linha, 7) = Worksheets("base").Cells(I, 22)
    Worksheets("AÇÕES PDCA FECHADAS").Cells(Linha, 8) = Worksheets("base").Cells(I, 23)
    Worksheets("AÇÕES PDCA FECHADAS").Cells(Linha, 9) = Worksheets("base").Cells(I, 25)
    Worksheets("AÇÕES PDCA FECHADAS").Cells(Linha, 10) = Worksheets("base").Cells(I, 25)
    Worksheets("AÇÕES PDCA FECHADAS").Cells(Linha, 11) = Worksheets("base").Cells(I, 26)
    Worksheets("AÇÕES PDCA FECHADAS").Cells(Linha, 12) = Worksheets("base").Cells(I, 27)
   End If
  Next I

End Sub