Automatically delete row after cut and paste

[PCloadletter]

Hello,

In Excel 2016, Is there a way to automatically delete a blank row after you cut and paste data to another worksheet?

My situation is this :

In our workbook we have one tab of data of active persons. Another tab is for cancellations.

When someone cancels out, we cut and paste from the active tab to the cancelled tab

If there are multiple cancellations, it leaves behind multiple blank rows which is tedious to go back to remove and sometimes is not done.

Can the blank row be automatically “moved up”?

Thank you.

 

[Joe4]

Welcome to the Board!

We can create a macro with will automatically Cut, Paste, and Delete for you.
We would just need to know the specifics, such as:
1. What are the sheet names?
2. What columns are being copied over?
3. Where does the line getting pasted go (is it below the last row of data on the other sheet)?
4. How would you like the user to select the row (we could have it just run against the selected row, or we could prompt the user to enter a row number)?

 

[PCloadletter]

Welcome to the Board!

We can create a macro with will automatically Cut, Paste, and Delete for you.
We would just need to know the specifics, such as:
1. What are the sheet names?
2. What columns are being copied over?
3. Where does the line getting pasted go (is it below the last row of data on the other sheet)?
4. How would you like the user to select the row (we could have it just run against the selected row, or we could prompt the user to enter a row number)?

Thank you for the quick reply!

1. Sheet 1 : BH ACTIVE, Sheet 2 : Cancellations

There are actually a couple dozen other “active” sheets too.

2. A to X

3. Yes, it would goto the bottom line on the cancellation sheet

4. First option, have the user select the row.

Thank you again!

 

[Joe4]

OK, give this VBA code a run,

Sub MyCopy()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rw1 As Long, rw2 As Long
    
'   Set worksheets
    Set ws1 = Sheets("BH Active")
    Set ws2 = Sheets("Cancellations")
    
'   Get row selected
    rw1 = ActiveCell.Row
    
'   Find next available row on second sheet by looking at column A
    rw2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Copy from first sheet and paste to second
    ws1.Range(Cells(rw1, "A"), Cells(rw1, "X")).Copy ws2.Cells(rw2, "A")
    
'   Delete row from first sheet
    ws1.Rows(rw1).Delete

End Sub

 

[PCloadletter]

Thank you very much, this worked perfectly.

My other question would be, how would I get this to work if the workbook has multiple sheets? (some of my workbooks have 30+ sheets).

Regards

 

[Joe4]

What would be multiple?
The sheets were you are cutting from, or the sheets where you are pasting to?

 

[PCloadletter]

Sorry, should have specified. Multiple sheets that I would cut and paste into one sheet (cancellations).

 

[Joe4]

I think if you just change this line:

    Set ws1 = Sheets("BH Active")

to this:

    Set ws1 = ActiveSheet

it should do what you want.

 

[PCloadletter]

Sorry for the late reply but this worked beautifully.

Thank you.

 

[Joe4]

You are welcome.