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

 

[Fluff]

Change this

.Range("A" & i).Resize(, 3).Copy Worksheets("Sheet2").Range("B" & LastRowSheet2 + 1)

to

.Range("A" & i).Resize(, 3).Copy 
Worksheets("Sheet2").Range("B" & LastRowSheet2 + 1).PasteSpecial xlPasteValues

 

[JayCar]

Thanks! I´m trying to do the same for complete rows - from sheet1 to sheet3 -, as below:

# For i = 2 To LastRowSheet1 Step 1
LastRowSheet3 = Worksheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
.Rows(i).Copy Worksheets("Sheet3").Range("A" & LastRowSheet3 + 1).PasteSpecial xlPastevalues
Next i#

But is giving a compile error "Expected: end of statement" . Not sure what´s going on here...

 

[Fluff]

Can you please post the entire code & indicate which line is giving the error.

Also you do not key in a # symbol, you click the # icon in the reply window & paste the code between the tags that appear.

 

[JayCar]

Hi! Sorry for not copying the code in the right manner.See below de code:

Sub SummaryTable()
'========================================================================
' COPIES ALL SHEETS TO THE SUMMARY SHEET
'========================================================================
    Worksheets("Sheet3").Range("A2:u1000").Clear
    Dim LastRowSheet1, LastRowSheet2, LastRowSheet4, LastRowSheet3 As Long
    Dim i As Long
    Application.ScreenUpdating = False
'    LastRowSheet3 = Worksheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
'    Sheets("Sheet3").Range("A2:u" & LastRowSheet3).ClearContents
    LastRowSheet1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    With Worksheets("Sheet1")
        For i = 2 To LastRowSheet1 Step 1
                LastRowSheet3 = Worksheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
                .Rows(i).Copy Worksheets("Sheet3").Range("A" & LastRowSheet3 + 1).PasteSpecial xlPasteValues
            :=False, Transpose:=False
        Next i
    End With
    LastRowSheet2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    With Worksheets("Sheet2")
        For i = 2 To LastRowSheet2 Step 1
                LastRowSheet3 = Worksheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
                .Rows(i).Copy Worksheets("Sheet3").Range("A" & LastRowSheet3 + 1).PasteSpecial xlPasteValues
        Next i
    End With
    LastRowSheet4 = Worksheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Row
    With Worksheets("Sheet4")
        For i = 2 To LastRowSheet4 Step 1
                LastRowSheet3 = Worksheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
                .Rows(i).Copy Worksheets("Sheet3").Range("A" & LastRowSheet3 + 1).PasteSpecial xlPasteValues
        Next i
    End With

The error is related to line:

 .Rows(i).Copy Worksheets("Sheet3").Range("A" & LastRowSheet3 + 1).PasteSpecial xlPasteValues

Compile error: Expected end of statement.

 

[Fluff]

You need to remove this line

 :=False, Transpose:=False

 

[JayCar]

Opps, sorry it actually was deleted already and not working yet... Same issue in the same line.

 

[Fluff]

You need to remove this from each of those lines

.PasteSpecial xlPasteValues

Unless you want values only in which case it should be

                .Rows(i).Copy
                Worksheets("Sheet3").Range("A" & LastRowSheet3 + 1).PasteSpecial xlPasteValues

 

[JayCar]

ThX! I forgot the ENTER key... it worksnow!

 

[Fluff]

You're welcome & thanks for the feedback