VBA Macro - Move rows to other sheets

Sean_WS

New Member
Joined
Jul 6, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 
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
VBA Code:
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

[/CODE
[/QUOTE]
Hi Sorry for the delay guys, I tried using the above when I use the data validation drop down to select; 'Lost', 'Won' or 'Completed' it pops up saying 'Invalid outside procedure'?
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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...

VBA 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
I would be happy to use this if it were only me using the spreadsheet, but the sales people would use it and I would end up with 30 sheets.. Thank you though!!
 
Upvote 0
Hi Sorry for the delay guys, I tried using the above when I use the data validation drop down to select; 'Lost', 'Won' or 'Completed' it pops up saying 'Invalid outside procedure'?
Well, I have an error checking in this script Which should popup a message box if there is a problem.
Maybe you have more code here in this sheet.
Show me all the code you have in this sheet.
And this is a sheet change again script and should not be put in a button.
Did you install the script as I explained in my posting?
 
Upvote 0
Well, I have an error checking in this script Which should popup a message box if there is a problem.
Maybe you have more code here in this sheet.
Show me all the code you have in this sheet.
And this is a sheet change again script and should not be put in a button.
Did you install the script as I explained in my posting?
I tried downloading the XLBB to show you a mini sheet but the companies computer wouldn't allow it.. would it be possible to send you a copy of the document to take a look at? If not that's totally understandable
 
Upvote 0
I tried downloading the XLBB to show you a mini sheet but the companies computer wouldn't allow it.. would it be possible to send you a copy of the document to take a look at? If not that's totally understandable
I really do not need to see your sheet. But I would like for you to answer my questions and show me all the code you have in this sheet. I suspect you can more code in the sheet that causes the problem when both scripts are in the sheet.

My questions were:
Show me all the code you have in this sheet.
And this is a sheet change again script and should not be put in a button.
Did you install the script as I explained in my posting?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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