Excel copy rows from one sheet to another based on criteria

UUsh2991New MemberJoinedApr 30, 2017Messages5Apr 30, 2017#1Hi Everyone! I am looking to get some assistance with coding a macro to move data from one sheet to another and delete fr

Excel copy rows from one sheet to another based on criteria

U

Ush2991

New MemberJoinedApr 30, 2017Messages5
  • Apr 30, 2017
  • #1Hi Everyone! I am looking to get some assistance with coding a macro to move data from one sheet to another and delete from the original source sheet, when a criteria is met. In my current sheet, have the following columns:

    Date Issue Status Archive

    What I want to happen is when the button (with the macro is clicked), any rows with "Yes" indicated in the Archive column is to be moved to another sheet and deleted from the existing sheet. Ideally the code would read every single cell with "Yes" in the Archive column and move the data over.

    I am fairly new with macros, so any assistance on how to achieve this would be appreciated. Thank you in advance.
Michael M

Michael M

Well-known MemberJoinedOct 27, 2005Messages21,439Office Version
  1. 365
  2. 2007Platform
  3. Windows
  • Apr 30, 2017
  • #2Maybe this...assumes original data is in Sheet1 and is being copied to sheet2

    Code:Sub MM1() 'no header row Application.DisplayAlerts = False With Sheets("Sheet1").UsedRange .AutoFilter .AutoFilter field:=4, Criteria1:="Yes" .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1") .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Delete .AutoFilter End With Application.DisplayAlerts = True End Sub

Reactions: Ush2991U

Ush2991

New MemberJoinedApr 30, 2017Messages5
  • May 1, 2017
  • #3Thank you Michael. Your code works but there are a couple of small tweaks that I am still trying to figure out:
    - Every time I click on the macro with someone marked as "Yes" under the Archive column, the data on the second sheet that already exists is being overwritten (i.e. When I move two rows over in the first attempt and then the second time choose another two rows to archive, the second two rows are overwriting the first two)
    - When the Archive column cells are blank, all of the data is being deleted

    Please let me know if you can assist. Thanks again for your help!
Michael M

Michael M

Well-known MemberJoinedOct 27, 2005Messages21,439Office Version
  1. 365
  2. 2007Platform
  3. Windows
  • May 1, 2017
  • #4Try this...
    Code:Sub MM1() 'no header row Dim lr As Long Application.DisplayAlerts = False lr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row 'change sheet name to suit With Sheets("Sheet1").UsedRange .AutoFilter .AutoFilter field:=4, Criteria1:="Yes" .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A" & lr + 1) .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Delete .AutoFilter End With Application.DisplayAlerts = True End Sub

Reactions: Ush2991U

Ush2991

New MemberJoinedApr 30, 2017Messages5
  • May 2, 2017
  • #5Works like a charm. Thank you so much!U

Ush2991

New MemberJoinedApr 30, 2017Messages5
  • May 9, 2017
  • #6
Michael M said:Try this...
Code:Sub MM1() 'no header row Dim lr As Long Application.DisplayAlerts = False lr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row 'change sheet name to suit With Sheets("Sheet1").UsedRange .AutoFilter .AutoFilter field:=4, Criteria1:="Yes" .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A" & lr + 1) .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Delete .AutoFilter End With Application.DisplayAlerts = True End SubClick to expand...
I have one more question regarding the above mentioned code. When the "Yes" criteria is not met (i.e. no rows with a "Yes" in the archive column), the sheet/macro seems to be crashing. Is there way to add to the code where by if this situation is not met, the macro doesn't run? Please let me know. Thank you.
Michael M

Michael M

Well-known MemberJoinedOct 27, 2005Messages21,439Office Version
  1. 365
  2. 2007Platform
  3. Windows
  • May 9, 2017
  • #7Ok, try

    Code:Sub MM1() 'no header row Dim lr As Long If Application.WorksheetFunction.CountIf(Range("D:D"), "Yes") = 0 Then Exit Sub Application.DisplayAlerts = False lr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row 'change sheet name to suit With Sheets("Sheet1").UsedRange .AutoFilter .AutoFilter field:=4, Criteria1:="Yes" .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A" & lr + 1) .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Delete .AutoFilter End With Application.DisplayAlerts = True End SubLast edited: May 9, 2017U

Ush2991

New MemberJoinedApr 30, 2017Messages5
  • May 10, 2017
  • #8
Michael M said:Ok, try

Code:Sub MM1() 'no header row Dim lr As Long If Application.WorksheetFunction.CountIf(Range("D:D"), "Yes") = 0 Then Exit Sub Application.DisplayAlerts = False lr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row 'change sheet name to suit With Sheets("Sheet1").UsedRange .AutoFilter .AutoFilter field:=4, Criteria1:="Yes" .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A" & lr + 1) .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Delete .AutoFilter End With Application.DisplayAlerts = True End SubClick to expand...
I tried this but I keep getting Runtime Error 1004 - No Cells Found
Michael M

Michael M

Well-known MemberJoinedOct 27, 2005Messages21,439Office Version
  1. 365
  2. 2007Platform
  3. Windows
  • May 10, 2017
  • #9The "Archive" Column is "D" isn't it ??
    The code works fine for me !!

    Where are you pasting the code ??Last edited: May 10, 2017

Video liên quan