VBA Macro - Move rows to other sheets

[Sean_WS]

I have been tasked with building a sales tracker for my new company and I am trying include a macro to move projects that have been, 'Won' 'Lost' & 'Confirmed' into other sheets named the same and I cant get it to work, Most of the data is input manually or from drop downs.
The data starts from B5 in the 'Tracker' sheet with headers above across to U5 currently down to row 1009, However the Data in the spreadsheet is purely me testing it rather than actuall clients etc. so other than the top 10-15 lines there is no data.

Also, is it possible to move the rows to different sheets once they are moved? or will that require more macros?
Thanks in advance

 

[My Aswer Is This]

Are you wanting to move all these rows to their particular sheet when you press a button or move them when you enter the particular value into the sheet?

And sure, if you move them now and you can move them again if you want but would require more code sure.

 

[Sean_WS]

Are you wanting to move all these rows to their particular sheet when you press a button or move them when you enter the particular value into the sheet?

And sure, if you move them now and you can move them again if you want but would require more code sure.

just the one row at a time, I have column F which each have a drop down menu for the sales guys to select a project status which will move them hopefully

 

[My Aswer Is This]

So, if you enter "Won" into column F of master sheet you want this row copied to sheet named "Won"

And the same with the other mentioned values
And do you want to delete this row from the master sheet?

 

[Sean_WS]

So, if you enter "Won" into column F of master sheet you want this row copied to sheet named "Won"

And the same with the other mentioned values
And do you want to delete this row from the master sheet?

That is correct, thank you

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/6/2022  7:36:49 AM  EDT
If Target.Column = 6 Then
On Error GoTo M
Dim r As Long
Dim ans As String
r = Target.Row
Dim Lastrow As Long
ans = Target.Value
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(r).Copy Sheets(ans).Rows(Lastrow)
Rows(r).Delete
End If
Exit Sub
M:
MsgBox "We Had A Problem" & vbNewLine & "You entered. " & ans & vbNewLine & "This sheet name does not exist"

End Sub

 

[Jimmypop]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/6/2022  7:36:49 AM  EDT
If Target.Column = 6 Then
On Error GoTo M
Dim r As Long
Dim ans As String
r = Target.Row
Dim Lastrow As Long
ans = Target.Value
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(r).Copy Sheets(ans).Rows(Lastrow)
Rows(r).Delete
End If
Exit Sub
M:
MsgBox "We Had A Problem" & vbNewLine & "You entered. " & ans & vbNewLine & "This sheet name does not exist"

End Sub

Good one sir...

Might I also suggest the one below for Sean_WS... This takes whatever is entered into Column F and if sheet does not exist then it creates a sheet with that name and then continues original code...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/6/2022  7:36:49 AM  EDT Original code by My Aswer Is This
'Modified  7/6/2022  14:24:17 AM  EDT Updated by Jimmypop - Added funtionality so sheet is created automatically if not exists
    If Target.Column = 6 Then
        Dim r As Long
        Dim ans As String
        Dim newSheetName As String
        Dim checkSheetName As String
        Dim Lastrow As Long
        r = Target.Row
        ans = Target.Value
        newSheetName = ans
        On Error Resume Next
        checkSheetName = Worksheets(newSheetName).Name
        If checkSheetName = "" Then
            Worksheets.Add.Name = newSheetName
            MsgBox "The sheet named ''" & newSheetName & _
            "'' does not exist in this workbook but it has been created now.", _
            vbInformation, "System Message"
        Else
''Do nothing
        End If
        Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
        Rows(r).Copy Sheets(ans).Rows(Lastrow)
        Rows(r).Delete
    End If
    Exit Sub
End Sub

 

[My Aswer Is This]

Good one sir...

Might I also suggest the one below for Sean_WS... This takes whatever is entered into Column F and if sheet does not exist then it creates a sheet with that name and then continues original code...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/6/2022  7:36:49 AM  EDT Original code by My Aswer Is This
'Modified  7/6/2022  14:24:17 AM  EDT Updated by Jimmypop - Added funtionality so sheet is created automatically if not exists
    If Target.Column = 6 Then
        Dim r As Long
        Dim ans As String
        Dim newSheetName As String
        Dim checkSheetName As String
        Dim Lastrow As Long
        r = Target.Row
        ans = Target.Value
        newSheetName = ans
        On Error Resume Next
        checkSheetName = Worksheets(newSheetName).Name
        If checkSheetName = "" Then
            Worksheets.Add.Name = newSheetName
            MsgBox "The sheet named ''" & newSheetName & _
            "'' does not exist in this workbook but it has been created now.", _
            vbInformation, "System Message"
        Else
''Do nothing
        End If
        Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
        Rows(r).Copy Sheets(ans).Rows(Lastrow)
        Rows(r).Delete
    End If
    Exit Sub
End Sub

Well, that is an ideal. But if it were me, I would not like it.
If user enters anything in column F a new sheet is made if it's not already made.

 

[Jimmypop]

Also true... Maybe then a data validation drop down that only accepts what is required...

 

[My Aswer Is This]

Also true... Maybe then a data validation drop down that only accepts what is required...

I would assume that is what the user is using. But if not, then my script gives a warning with a Popup Message Box