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:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

JayCar

New Member
Joined
Feb 13, 2019
Messages
10
I mean I would like to run the macro from sheet 3 but not sure why it just Works in sheet 1.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
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
 

JayCar

New Member
Joined
Feb 13, 2019
Messages
10
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Which cells do you want to copy?
 

JayCar

New Member
Joined
Feb 13, 2019
Messages
10
Hi Fluff! I would like to copy columns A, B and C to B, D an E respectively. ThX!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

JayCar

New Member
Joined
Feb 13, 2019
Messages
10
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
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.
 

JayCar

New Member
Joined
Feb 13, 2019
Messages
10
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!

 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

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
Top