Need a copy macro from one sheet to another based on a criteria

profklein

New Member
Joined
May 20, 2017
Messages
20
Hello again.

I want to copy only some of the rows of data from one sheet to another. I want to have a macro in sheet y
that will look at the data in sheet x and copy only those rows that have a "YES" in, say, column AA.
The macro should start looking in row 2 in sheet x and starting copying in row 2 in sheet y.

Thank you to all who have helped me so far and all who will help me in the future on my path to a docorate.


Gene
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,727
Try:
Code:
Sub copyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("x")
    Set desWS = Sheets("y")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    srcWS.Range("AA1:AA" & LastRow).AutoFilter Field:=1, Criteria1:="YES"
    srcWS.Range("AA2:AA" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy desWS.Cells(2, 1)
    srcWS.Range("AA1").AutoFilter
    Application.ScreenUpdating = True
End Sub
The macro assumes you have headers in row 1 and the data starts in row 2.
 
Last edited:

profklein

New Member
Joined
May 20, 2017
Messages
20
I am trying to change the macro in two respects:
1) Instead of the criteria being the cell in column AA needs to be YES, The cell in column BG should NOT be 58
2) The macro should start on row 3 and not row 2

Thanks
Gene
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,727
The macro assumes you have headers in row 2.
Code:
Sub copyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("x")
    Set desWS = Sheets("y")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    srcWS.Range("BG2:BG" & LastRow).AutoFilter Field:=1, Criteria1:="<>58"
    srcWS.Range("BG3:BG" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy desWS.Cells(2, 1)
    srcWS.Range("AA1").AutoFilter
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,113,875
Messages
5,544,816
Members
410,635
Latest member
phoenix7771
Top