Code to move rows into another sheet based on certain values [Solved/Closed]

Question

Code to move rows into another sheet based on certain values [Solved/Closed]

Report

nathaliecannell - Mar 15, 2017 at 04:25 PM
vcoolio

Posts: 1300
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: July 20, 2021

- Mar 17, 2017 at 08:02 AM

I'm having trouble figuring out how to do something! I have a sheet (Sheet1) with customer details on it (columns A-D will have data in). I want to be able to write "Cancelled" in column E of the row in particular if the customer's contract has been cancelled and this cause the entire row to be cut and pasted into Sheet2 which will be for cancelled customers with the same column headers etc.

How do I do this? I'm not very good with Macro codes & where to input them!

Excel move row to another sheet based on cell value
Excel move row to another sheet based on cell value without macro - Best answers
Excel move row to another sheet based on cell value automatically - Best answers
Excel copy row to another sheet based on cell value ✓ - Forum - Excel
Copy rows based on a condition ✓ - Forum - Excel
Transfer Data From One Excel Sheet to Another - How-To - Excel
Copy a row from one worksheet to another, based on criteria ✓ - Forum - Excel
Excel move row to another sheet based on cell value without macro ✓ - Forum - Office Software

1 reply

Ask a question

vcoolio · Accepted Answer · 2017-03-17T08:02:57-0400

Hello Nathaliecannell,

You could try the following code:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Intersect(Target, Columns("E:E")) Is Nothing Then Exit Sub

Application.ScreenUpdating = False

        If Target.Value = "Cancelled" Then
        Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        Target.EntireRow.Delete
        End If
  
Sheet2.Columns.AutoFit

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code is a Worksheet_Change event and needs to be placed in the Worksheet (Sheet1) module. Once "Cancelled" is typed into a cell in Column E of Sheet1 and you click away (or press Enter or down arrow), the relevant row of data will be transferred to Sheet2 and the row will be deleted from Sheet1.

To implement the code, right click on the Sheet1 tab and select "View Code" from the menu that appears. In the big white field that then appears, paste the above code.

Test the code in a copy of your work book first.

I hope that this helps.

Cheerio,
vcoolio.

Code to move rows into another sheet based on certain values [Solved/Closed]

1 reply

Your reply

Subscribe To Our Newsletter!