MAKE MACRO/VBA TO MOVE ROW TO ANOTHER SPREADSHEET AND DELETE OLD ROW WHEN DATE AENTERED IN A CELL

Daleksec93

New Member
Joined
Aug 18, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hi Experts!

I have had a go at this myself and have only been able to do make this work when i initially put the code in. Does not continue to work there after.

I am trying to move a row to another sheet if I type "Z" in the "Job completed?" Column (A3-E218.) after row 3. row 3 is title on both sheet. sheet 1 is called CURRENT and sheet 2 is called REMOVED

I need this to happen instantly after typing "Z" in column A starting with row 4

Please let me know if there is any other information you will need to be able to help me with this.

Thank you!
 
Just start the code with
VBA Code:
Application.EnableEvents = False
and end it with
VBA Code:
Application.EnableEvents = True
otherwise
.Delete xlUp calls the code again.
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
@Alex Blakenburg thanks for the reminder :)
I think the EnableEvents = False needs to come after the potential Exit Sub with the "Z" test.

VBA Code:
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A4", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then
        If Target.Value <> "Z" Then Exit Sub
        Application.EnableEvents = False
        Dim lRow As Long
        lRow = Sheets("REMOVED").Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
        With Target.EntireRow
            .Copy Sheets("REMOVED").Cells(lRow, 1)
            .Delete xlUp
        End With
    End If
    Application.EnableEvents = True
End Sub
 
Upvote 0
Try this. Right click on the sheet tab name for the sheet "CURRENT", select View Code, copy the code below to the window that appears on the right of the screen. Save the file & test it by typing a "Z" in column A of the "CURRENT sheet. As always, test this with a copy of your file.

VBA Code:
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A4", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then
    If Target.Value <> "Z" Then Exit Sub
    Dim lRow As Long
    lRow = Sheets("REMOVED").Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
    With Target.EntireRow
        .Copy Sheets("REMOVED").Cells(lRow, 1)
        .Delete xlUp
    End With
    End If
End Sub
Thank you it worked you dont by happen to know one for automatic aphabetize do you ? titles on row to start b3
 
Upvote 0
Thank you it worked you dont by happen to know one for automatic aphabetize do you ? titles on row to start b3
I'm sorry but I don't understand your question. Perhaps if you could provide a before-and-after example?
 
Upvote 0
I'm sorry but I don't understand your question. Perhaps if you could provide a before-and-after example?
Such as im typing in the name in column B and once i hit enter or change selected cell it automatically alphabetizes with the rest of the list.(and moves the row with it)
 
Upvote 0
Such as im typing in the name in column B and once i hit enter or change selected cell it automatically alphabetizes with the rest of the list.(and moves the row with it)
By "alphabetizes", do you mean sort on column B in alphabetical order? If so, do you want an all-encompassing code to do this and perform the move following a "Z" in column A?
 
Upvote 0
By "alphabetizes", do you mean sort on column B in alphabetical order? If so, do you want an all-encompassing code to do this and perform the move following a "Z" in column A?
by alphabetize i mean move the rows in alphabetical order based on column B header name when name is put in to cell and you click off it will have all information move itself to put itself in the right alphabetical order. the other information in that same row will move with their column 2 names
 
Upvote 0
You haven't said whether you want to incorporate that new function with the code provided in post #12 - so I'm going to assume that you do (let me know if that wasn't what you wanted. Replace the existing code with this:

VBA Code:
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("CURRENT")
    Set ws2 = Worksheets("REMOVED")
    Dim LRowCUR As Long, LRowREM As Long
    LRowCUR = ws1.Cells.Find("*", , xlFormulas, , 1, 2).Row
    LRowREM = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
    
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A4:B" & LRowCUR), Target) Is Nothing Then
        Select Case Target.Column
            Case Is = 1
                If Target.Value = "Z" Then
                    Application.EnableEvents = False
                    With Target.EntireRow
                        .Copy ws2.Cells(LRowREM, 1)
                        .Delete xlUp
                    End With
                End If
            Case Is = 2
                Application.EnableEvents = False
                ws1.Range("B3").CurrentRegion.Sort Key1:=ws1.Range("B3"), order1:=xlAscending, Header:=xlYes
        End Select
    End If
    Application.EnableEvents = True
End Sub
 
Upvote 0
Im sorry Yes I want the information Z'd out and moved over to the other sheet automatically Aphabetized when it does. But i also need an auto aphabetizer to my first sheet that has nothing to do with the Z
 
Upvote 0
Im sorry Yes I want the information Z'd out and moved over to the other sheet automatically Aphabetized when it does. But i also need an auto aphabetizer to my first sheet that has nothing to do with the Z
OK, try the code in post #18 and let me know how you go.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top