Copying rows from one table that match one criteria and pasting into another Excel sheet

Question

Copying rows from one table that match one criteria and pasting into another Excel sheet

Navigation

#1 by (2 votes)
#2 by (0 votes)

3

Hello! I am something new in VBA and I 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 feature. I'll try to be clear on my goal.

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

Thank you in advance for your help!

excel vba excel-vba

asked by anonymous 28.02.2018 / 14:54

2 answers

Method Math.Round does not round correctly What are delegates?

score 2 · Answer 1

As you did not give an example of the code I mounted this one from here with what you passed. Take the tests or check out the logic here and try to apply to your case.

Enter the following code in the worksheet to have the values parsed 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

score 0 · Answer 2

One way is to filter on coluna Z of the table by CLOSED and copy the entire visible area and then clean it. This is 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