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

JayCar

New Member
Joined
Feb 13, 2019
Messages
10
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?
Code:
'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
 
Last edited by a moderator:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I mean I would like to run the macro from sheet 3 but not sure why it just Works in sheet 1.
 
Upvote 0
You didn't qualify the Cells or Rows
Code:
    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
 
Upvote 0
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?
 
Upvote 0
Which cells do you want to copy?
 
Upvote 0
Try
Code:
    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
 
Upvote 0
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
 
Upvote 0
As you are copying the data into col B you need to change this, as shown
Code:
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.
 
Upvote 0
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!

 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top