Moving rows to different sheets based on drop down.

[purevega]

Hello! Thank you in advance for visiting my post. I understand there have been a couple posts here already that ask for help regarding VBA code and moving data from one sheet to another based on a drop-down value, I appreciate your help nonetheless.

----------

We are approaching territory changes for our sales team, and the current process takes 4-5 months to complete which we are looking to cut down significantly. I believe we can achieve a more efficient process by introducing VBA code into our excel sheets and moving rows of data from one sheet to another (or from account manager to account manager). I've watch a beginner's tutorial on VBA code (and macros) which has been helpful but I believe what I'm trying to achieve is more advanced than my current knowledge and I'm willing to learn.

Below is a screenshot of an example sheet that includes the basic information we use for territory changes (although missing rollup totals of their entire portfolio), where we would move the respective rows to the individual selected in the drop down menu in column "E" to the end of the data of the respective person receiving the new account (see highlighted row in the second screenshot). Each sheet titled "Person X" has the same layout you see below as well.

Screenshot 1

Screenshot 2

I'm looking for help creating VBA code that would CUT/PASTE a row of data from one sheet to another that would eventually contribute to a roll-up total of column C somewhere on the sheet (could be the top or bottom, doesn't matter). Although I provided the basic information we use for territory changes, there may be some additional rows to be included to help with decision making - so a helpful note in the code where I can extend the selection of rows being cut/paste would be nice.


I'm extremely thankful for any help regarding this, and I'm looking forward to learning how VBA code works further and adding it to my arsenal of skills

 

[Cubist]

Consider using the FILTER function before adding VBA.

Book2
	A	B	C	D	E
1	Person 1 Sheet
2	Account Name	Billing Country	Annual Spend	FROM	TO
3	Test 1	Antartica	500	Person 1
4	Test 2	Antartica	3200	Person 3	Person 2
5	Test 3	Antartica	900	Person 5
6	Test 4	Antartica	1000	Person 1	Person 2
7
8
9	Person 2 Sheet
10	Test 2	Antartica	3200	Person 3	Person 2
11	Test 4	Antartica	1000	Person 1	Person 2
Sheet4

Cell Formulas
Range	Formula
A10:E11	A10	=FILTER(A2:E6,E2:E6="Person 2")
Dynamic array formulas.

If you want VBA, when do you want the macro to trigger? If you want it to trigger upon the drop-down select on E, it's very risky if you select the wrong person. It's not reversible.

 

[purevega]

Consider using the FILTER function before adding VBA.

Book2
	A	B	C	D	E
1	Person 1 Sheet
2	Account Name	Billing Country	Annual Spend	FROM	TO
3	Test 1	Antartica	500	Person 1
4	Test 2	Antartica	3200	Person 3	Person 2
5	Test 3	Antartica	900	Person 5
6	Test 4	Antartica	1000	Person 1	Person 2
7
8
9	Person 2 Sheet
10	Test 2	Antartica	3200	Person 3	Person 2
11	Test 4	Antartica	1000	Person 1	Person 2
Sheet4

Cell Formulas
Range	Formula
A10:E11	A10	=FILTER(A2:E6,E2:E6="Person 2")
Dynamic array formulas.

If you want VBA, when do you want the macro to trigger? If you want it to trigger upon the drop-down select on E, it's very risky if you select the wrong person. It's not reversible.

Hello!

Thank you for your reply. I understand that it's not reversible, but theoretically if it was on another sheet you could select the drop down again and it would move. Can you elaborate on the FILTER function and how I may be able to utilize this?

 

[Cubist]

I've shown an example of FILTER in post #2. Rows 1-6 is your master sheet. Rows 9-11 are the result of Person 2. You would change "Person 2" for each different sheet.

 

[purevega]

I've shown an example of FILTER in post #2. Rows 1-6 is your master sheet. Rows 9-11 are the result of Person 2. You would change "Person 2" for each different sheet.

Is there a way to use multiple sheets while using the FILTER function like in my original screenshots? Would like the individual's data separated if possible.

 

[Cubist]

I see you're trying to pass back and forth the row rather than assign it from a master list. Try this. The code is to be inserted in all Sheet code (right-click on the sheet name -> View Code -> Paste. Repeat for all "Person" sheets).

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim selectedName As String
    Dim currentRow As Long
    Dim lastCell As Range
 
    If Not Intersect(Target, Me.Columns("E")) Is Nothing Then
        If Target.Cells.Count = 1 Then
            selectedName = Target.Value
         
            On Error Resume Next
            Set ws = ThisWorkbook.Sheets(selectedName)
            On Error GoTo 0
         
            If Not ws Is Nothing Then
                Application.EnableEvents = False
             
                currentRow = Target.Row
                Set lastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
                                            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
                If Not lastCell Is Nothing Then
                    lastRow = lastCell.Row + 1
                Else
                    lastRow = 1
                End If
             
                ws.Range("A" & lastRow) = Me.Range("A" & currentRow & ":E" & currentRow)
                Me.Rows(currentRow).Delete
                Application.EnableEvents = True
            Else
                MsgBox "Sheet named '" & selectedName & "' does not exist.", vbExclamation
            End If
        End If
    End If
End Sub

 

[purevega]

I see you're trying to pass back and forth the row rather than assign it from a master list. Try this. The code is to be inserted in all Sheet code (right-click on the sheet name -> View Code -> Paste. Repeat for all "Person" sheets).

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim selectedName As String
    Dim currentRow As Long
    Dim lastCell As Range
 
    If Not Intersect(Target, Me.Columns("E")) Is Nothing Then
        If Target.Cells.Count = 1 Then
            selectedName = Target.Value
        
            On Error Resume Next
            Set ws = ThisWorkbook.Sheets(selectedName)
            On Error GoTo 0
        
            If Not ws Is Nothing Then
                Application.EnableEvents = False
            
                currentRow = Target.Row
                Set lastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
                                            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
                If Not lastCell Is Nothing Then
                    lastRow = lastCell.Row + 1
                Else
                    lastRow = 1
                End If
            
                ws.Range("A" & lastRow) = Me.Range("A" & currentRow & ":E" & currentRow)
                Me.Rows(currentRow).Delete
                Application.EnableEvents = True
            Else
                MsgBox "Sheet named '" & selectedName & "' does not exist.", vbExclamation
            End If
        End If
    End If
End Sub

This is wonderful, thank you so much! I'll attempt to digest the VBA and try to learn what is doing exactly myself so I have a deeper understanding.

Should I change anything in the code from sheet to sheet or is it g2g?

 

[Cubist]

It should be good if it's exactly like your examples in the OP.

A few potential changes if things are different.
This line triggers when there's a change in column E.

If Not Intersect(Target, Me.Columns("E")) Is Nothing Then

This line moves values from column A:E.

 ws.Range("A" & lastRow) = Me.Range("A" & currentRow & ":E" & currentRow)

The dropdown selection must match exactly with the sheet names.

 

[purevega]

It should be good if it's exactly like your examples in the OP.

A few potential changes if things are different.
This line triggers when there's a change in column E.

If Not Intersect(Target, Me.Columns("E")) Is Nothing Then

This line moves values from column A:E.

 ws.Range("A" & lastRow) = Me.Range("A" & currentRow & ":E" & currentRow)

The dropdown selection must match exactly with the sheet names.

I changed the "E" Value to "F" since I have one more line of data on the actual sheet, and when I select the drop down to "Person 2", the data disappears and doesn't cut/paste into sheet dubbed "Person 2".

 

[Cubist]

Do you mean an extra column?
Do you have more columns than A-F?
What does your actual data look like?