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

Asked

Viewed 3,532 times

3

Hello! I am something new in VBA and I have obtained what I want through the macros recorder. However, I think that in this case I will not get what I want through this resource. I will try to be clear in my goal.

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

Thanks in advance for all the help!

excel vba excel-vba

  • Good Morning. Stack Overflow focuses on questions and answers to concrete cases where there has been difficulty in developing a solution. Have you developed any code? Have you tried something that didn’t work? From what I’ve seen it seems like what you want is a ready-made solution, but here the collaboration is for questions. Study some basic VBA and draft your own solution. When trying to implement it before, if there is an error come back here we help.

2 answers

2

As you did not give an example of the code I rode this from here with what you went through. Do the tests or check out the logic here and try to apply to your case.

Insert the following code into the worksheet 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

  • I made some changes. So if you tested before and did not succeed, test again.

0

One way is to filter in coluna Z table by CLOSED and copy the entire visible area and then clean it. So it’s more optimized than iterating one by one.

Dim a3 As Worksheet, FECHADA As Worksheet
    Set a3 = ThisWorkbook.Sheets("A3")
    Set FECHADA = ThisWorkbook.Sheets("AÇÕES PDCA FECHADAS")

    With a3
        'Limpa os Autofiltros da Planilha para evitar erros
        If .FilterMode Then
            .ShowAllData
        End If
        'Última Linha da coluna Z
        LastRow = .Cells(.Rows.Count, "Z").End(xlUp).Row
        LastRow2 = FECHADA.Cells(.Rows.Count, "A").End(xlUp).Row
        'AutoFiltro
        .Range(.Cells(10, 16), .Cells(LastRow, 27)).AutoFilter Field:=11, Criteria1:="CLOSED"
        'Cria range com as células visíveis após Filtrar
        On Error Resume Next
        Set VisibleRange = .Range(.Cells(10, 16), .Cells(LastRow, 27)).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not VisibleRange Is Nothing Then
            'Copia as células visíveis após aplicação do AutoFiltro
            'E cola na Planilha de destino
            VisibleRange.Copy Destination:=FECHADA.Cells(LastRow2+1,1)
        End If

        'Limpa a Range
        VisibleRange.Clear

        If .FilterMode Then
            .ShowAllData
        End If

    End With

Browser other questions tagged excel vba excel-vba

You are not signed in. Login or sign up in order to post.