Copy row from sheet 1 to next row in sheet 2. delete from sheet 1 and move rows up

**papabob1954** · 12-14-2012, 05:17 PM

Workbook with sheets Assigned and Completed. I want to be able to select a row from sheet Assigned (not always the first row) and move the data to sheet Completed in the next available row (archiving completed work). I want the selected row to then be deleted after it has been moved and any rows that were under it to move up so there are no empty rows. I've found many different variations but have not found one that does all of what I want. My sheet has 26 columns that need to be copied/moved.

I have a procedure to send an email to my group that has the Assigned information (different people have different pieces of the assignment) in a workbook that I am trying to clean up. I won't know if it works until I get the sheets thing figured out.

Thanks in advance for your help.

**Trebor76** · 12-14-2012, 05:26 PM

Hi papabob1954,

Welcome to the forum!!

What's the flag to determine whether a row is moved from the Assigned tab to the Completed tab?

Robert

**papabob1954** · 12-14-2012, 06:27 PM

I was thinking an x in an empty cell in front of the data

**Trebor76** · 12-14-2012, 06:30 PM

No, I mean how do you know to move a row from the Assigned tab to the Completed - what's the indicator (flag) that tells you that the work is now completed?

**papabob1954** · 12-14-2012, 06:34 PM

After I enter the data in all the columns, then send the email to the group with that data, it is completed. I have assigned the work to the people in my group.

**Trebor76** · 12-14-2012, 07:12 PM

So after the email is sent all the data in Assigned tab should be moved to the Completed tab leaving no rows in the Assigned tab?

**papabob1954** · 12-14-2012, 07:24 PM

The selected row on the Assigned tab gets moved. There will be data for other assignments that I need to assign on the Assigned tab. So like I am assigning devices to test. each device has multiple pieces to test and so I will enter a name under each piece and then send the assignment email out. I may have 5 or 6 devices at one time to assign and I want to look at the spreadsheet to make sure I haven't over/under assigned a person. If I'm good with that, I'll send the email, one device at a time.

**Trebor76** · 12-14-2012, 07:45 PM

OK, let me know how this goes (just initially try it on a copy of your data):

Option Explicit
Sub Macro1()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au

    Dim lngActiveRow As Long, _
        lngPasteRow As Long

    lngPasteRow = Sheets("Completed").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    lngActiveRow = Sheets("Assigned").Cells(ActiveCell.Row, "A").Row
    
    With Sheets("Assigned").Rows(lngActiveRow).EntireRow
        .Copy Sheets("Completed").Rows(lngPasteRow)
        .Delete
    End With
    
End Sub

Regards,

Robert

**papabob1954** · 12-16-2012, 08:08 PM

Thanks Robert. That worked perfectly. I was able to import my sendemail macro over to my new spreadsheet.

**Trebor76** · 12-16-2012, 09:38 PM

You're welcome - I'm glad we were able to get it sorted for you.

If you could mark the thread as solved when you get a moment, it would be appreciated.

Regards,

Robert

Copy row from sheet 1 to next row in sheet 2. delete from sheet 1 and move rows up

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy row from sheet 1 to next row in sheet 2. delete from sheet 1 and move rows up

Re: Copy row from sheet 1 to next row in sheet 2. delete from sheet 1 and move rows up

Re: Copy row from sheet 1 to next row in sheet 2. delete from sheet 1 and move rows up

Re: Copy row from sheet 1 to next row in sheet 2. delete from sheet 1 and move rows up

Re: Copy row from sheet 1 to next row in sheet 2. delete from sheet 1 and move rows up

Re: Copy row from sheet 1 to next row in sheet 2. delete from sheet 1 and move rows up

Re: Copy row from sheet 1 to next row in sheet 2. delete from sheet 1 and move rows up

Re: Copy row from sheet 1 to next row in sheet 2. delete from sheet 1 and move rows up

Re: Copy row from sheet 1 to next row in sheet 2. delete from sheet 1 and move rows up

Re: Copy row from sheet 1 to next row in sheet 2. delete from sheet 1 and move rows up

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions