Macro to copy rows from a sheet to another based on a criteria

[JayCar]

Hi there,

I have limited programming knowledge but I manage to créate this code below. Rows from sheet1 are copied in sheet2 if column E is "YES". This Works but only If I run the Macro in sheet 1.Anybody knows why?

'Sub CopyRowToSheet2()
'========================================================================
' COPIES ALL ROWS WHERE COLUMN E HAS VALUE "YES" FROM MainSheet
' INTO "SHEET2" SHEET
'========================================================================
    Worksheets("Sheet2").Range("A2:E1000").Clear
    Dim LastRowSheet1, LastRowSheet2 As Long
    Dim i As Long
    Application.ScreenUpdating = False
'    LastRowSheet2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
'    Sheets("Sheet2").Range("A2:E" & LastRowSheet2).ClearContents
    LastRowSheet1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    With Worksheets("Sheet1")
        For i = 2 To LastRowSheet1 Step 1
            If Cells(i, "E").Value = "YES" Then
                LastRowSheet2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
                Rows(i).Copy Worksheets("Sheet2").Range("A" & LastRowSheet2 + 1)
            End If
        Next i
    End With
    Application.ScreenUpdating = True
    Sheet3.Select
    
End Sub

 

[JayCar]

I mean I would like to run the macro from sheet 3 but not sure why it just Works in sheet 1.

 

[Fluff]

You didn't qualify the Cells or Rows

    With Worksheets("Sheet1")
        For i = 2 To LastRowSheet1 Step 1
            If [COLOR=#ff0000].[/COLOR]Cells(i, "E").Value = "YES" Then
                LastRowSheet2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
                [COLOR=#ff0000].[/COLOR]Rows(i).Copy Worksheets("Sheet2").Range("A" & LastRowSheet2 + 1)
            End If
        Next i
    End With

Note the . in front of them

 

[JayCar]

Great! Thanks for solving this! Do you know how I can manage to copy only specific cells of the row instead of the whole row?

 

[Fluff]

Which cells do you want to copy?

 

[JayCar]

Hi Fluff! I would like to copy columns A, B and C to B, D an E respectively. ThX!

 

[Fluff]

Try

    With Worksheets("Sheet1")
        For i = 2 To LastRowSheet1 Step 1
            If .Cells(i, "E").Value = "YES" Then
                LastRowSheet2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
                .Range("A" & i).Resize(, 3).Copy Worksheets("Sheet2").Range("B" & LastRowSheet2 + 1)
            End If
        Next i
    End With

 

[JayCar]

Hi Fluff, that´s not working. I´m just getting C column of one of the rows (even if more are "YES" in E). That´s what I includeed in the code:

Sub CopyRowToSheet3()
'========================================================================
' COPIES ALL ROWS WHERE COLUMN E HAS VALUE "YES" FROM MainSheet
' INTO "SHEET2" SHEET
'========================================================================
Worksheets("Sheet2").Range("A2:E1000").Clear
Dim LastRowSheet1, LastRowSheet2 As Long
Dim i As Long
Application.ScreenUpdating = False
' LastRowSheet2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
' Sheets("Sheet2").Range("A2:E" & LastRowSheet2).ClearContents
LastRowSheet1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With Worksheets("Sheet1")
For i = 2 To LastRowSheet1 Step 1
If .Cells(i, "E").Value = "YES" Then
LastRowSheet2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & i).Resize(, 3).Copy Worksheets("Sheet2").Range("B" & LastRowSheet2 + 1)
End If
Next i
End With
Application.ScreenUpdating = True
Sheet3.Select

End Sub

 

[Fluff]

As you are copying the data into col B you need to change this, as shown

LastRowSheet2 = Worksheets("Sheet2").Range("[COLOR=#ff0000]B[/COLOR]" & Rows.Count).End(xlUp).Row

Also when posting code please use code tags, the # icon in the reply window.

 

[JayCar]

Thanks, it worked! Sorry for the delay…andfor the code # stuff – I was not aware of how to do it -. I´m also wonderinghow I can make that the rows copied are just copied as values instead as includingtheformulas… What would be the changes in the code? Thx!