VBA: Copy entire row with the condition TRUE to TempSheet2

Question

Can someone help me with an VBA script that copy entire rows with the condition TRUE from column U in TempSheet over to TempSheet2.

there are literally hundreds of those questions already answered on this site. Please search — , Feb 17 '15 at 09:40
possible duplicate of [Vba macro to copy row from table if value in table meets condition](http://stackoverflow.com/questions/12177125/vba-macro-to-copy-row-from-table-if-value-in-table-meets-condition) — Alessandro Da Rugna, Feb 17 '15 at 09:45

score 0 · Answer 1 · answered Feb 17 '15 at 12:08

use something like this:

Sub test()
    Dim i&, z&, oCell As Range
    Application.ScreenUpdating = 0
    z = 1: i = Sheets("TempSheet").Cells(Rows.Count, "U").End(xlUp).Row
    For Each oCell In Sheets("TempSheet").Range("U1:U" & i)
        If oCell.Value = True Then
            oCell.EntireRow.Copy Sheets("TempSheet2").Rows(z)
            z = z + 1
        End If
    Next
    Application.ScreenUpdating = 1
End Sub

score -1 · Accepted Answer · answered Feb 17 '15 at 11:13

This macro checks each row for the value "True" in the U column. The columns with true value in column U as then copied to the other sheet.

Option Explicit


Sub CopyRow()
    Dim Row As Integer
    Dim sRow As String
    Dim i As Long

    Application.ScreenUpdating = False

    i = 1 'To ensure each time the macro is run it starts at row 1

    For i = 1 To 1048576 'for each row in the sheet

        If Range("U" & i).Value = True Then 'If the U value is true then copy it

            Row = i
            sRow = CStr(Row) 'convert row number to string
            Rows(sRow & ":" & sRow).Select
            Selection.Copy
            Sheets("Sheet2").Select
            Rows(sRow & ":" & sRow).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
            Application.CutCopyMode = False

        End If
    Next i

    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

I am uncertain as to how you want the macro triggered but a worksheet change may suit.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' trigger the macro if they are changed
    Set KeyCells = Range("U:U")
    Call CopyRow
    End If
End Sub

Note: Worksheet_Change goes in the code for sheet1 and the macro goes in a module

There is probably a neater way of copying the rows without making them active first. — JohnCitezen, Feb 17 '15 at 11:16

VBA: Copy entire row with the condition TRUE to TempSheet2

2 Answers2