+ Reply to Thread
Results 1 to 2 of 2

Copy rows from one sheet to another, then deleting the original rows

  1. #1
    Registered User
    Join Date
    03-19-2009
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2011
    Posts
    37

    Copy rows from one sheet to another, then deleting the original rows

    Hi, I am trying to create a macro that does the following:

    copies rows from a worksheet called "Action Items" to a worksheet called "Completed Action Items"

    The rows to be copied will have the word "Complete" in column A.
    After the rows have been copied to the "Completed Action Items" sheet I would like them removed from the "Action Items" worksheet.

    This is what I have so far - but for some reason it doesn't seem to filter by the word "Complete" in column A. Also, the "selection.Cut" isn't working because it is selecting multiple ranges (after applying the autofilter).

    Any ways around this?
    I'm not sure if the rest of the marco after the selection.cut is correct or not - just my best/first guess.

    I have attached an example workbook. I am also trying to do the same thing with cancelled action items. I would like them to move to the Cancelled Action Item sheet.

    Thanks so much ahead of time! This will be a huge time saver for me and my team.

    Sub PasteRowDelete()
    Application.ScreenUpdating = False
    Worksheets("Action Items").Select

    'Select Range
    '(note you can change this to meet your requirements)
    Range("A3:J300").Select
    'Apply Autofilter
    Selection.AutoFilter
    FilterCriteria = Complete
    'NOTE - this filter is on column A (field:=1), to change
    'to a different column you need to change the field number
    Selection.AutoFilter field:=1, Criteria1:=FilterCriteria
    'Select the visible cells (the filtered data)
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Cut
    Sheets("Complete Action Items").Select
    Range("A65536").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    'Clear the clipboard contents
    Application.CutCopyMode = False
    'Go back to the original tab
    Sheets("Action Items").Select
    'Clear the autofilter
    Selection.AutoFilter field:=1
    'Take the Autofilter off
    Selection.AutoFilter
    Selection.Delete Shift:=xlUp
    ActiveCell.Select
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-19-2009
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2011
    Posts
    37

    Re: Copy rows from one sheet to another, then deleting the original rows

    I got it!

    Sub PasteRowDelete()
    Application.ScreenUpdating = False
    Worksheets("Action Items").Select

    'Select Range
    ActiveSheet.Range("$A$3:$J$300").AutoFilter Field:=1, Criteria1:="Complete"
    'NOTE - this filter is on column A (field:=1), to change
    'to a different column you need to change the field number
    Range("A4:I500").Select
    Selection.Copy
    Sheets("Complete Action Items").Select
    Range("A300").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    'Clear the clipboard contents
    Application.CutCopyMode = False
    'Go back to the original tab
    Sheets("Action Items").Select
    Selection.Delete Shift:=xlUp
    'Clear the autofilter
    Selection.AutoFilter Field:=1
    'Take the Autofilter off
    Selection.AutoFilter
    Application.ScreenUpdating = True
    End Sub


    thanks anyway!
    Last edited by wace; 08-06-2009 at 01:25 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1