Move data to the right tab

outlawspeeder

Board Regular
Joined
Jan 17, 2009
Messages
225
Office Version
  1. 2019
I have on sheet 1 A10:A20 and B10:B20.

If on sheet 2 Column D matches A10:A20 the row is moved to sheet 3, if matches B10:B20 move the row to sheet 4, If it doesn't match at all mve to sheet 5.


I have been using a pivot data for this but I know there is a faster way that will have less errors.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hello outlawspeeder,
Is this something that gets done routinely?
When you say If on sheet 2 Column D matches A10:A20, do you mean sheet2 range D10:D20? (or all of column D?)
When you say you want the row to move, which row? (from which sheet?)
Do you want it to get copied & pasted, or cut & pasted?

Is vba an option for you?
 
Upvote 0
try following macro code
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 Then
        chkData Sheet2.Cells(Target.row, 4).Value
    End If
End Sub
Private Sub chkData(str As String)
    Dim tmp As Boolean
    tmp = False
    
    Dim row As Integer, col As Integer
    row = 10
    col = 1
    
    Dim row3 As Integer, col3 As Integer
    row3 = 1
    col3 = 1
    
    Dim row4 As Integer, col4 As Integer
    row4 = 1
    col4 = 1
    
     Dim row5 As Integer, col5 As Integer
    row5 = 1
    col5 = 1
    
    For row = 10 To 20
        If str = Sheet1.Cells(row, col).Value Then
            Sheet3.Cells(row3, col3).Value = Sheet1.Cells(row, col).Value
            Sheet3.Cells(row3, col3 + 1).Value = Sheet1.Cells(row, col + 1).Value
            row3 = row3 + 1
            
        ElseIf str = Sheet1.Cells(row, col + 1).Value Then
            Sheet4.Cells(row4, col4).Value = Sheet1.Cells(row, col).Value
            Sheet4.Cells(row4, col4 + 1).Value = Sheet1.Cells(row, col + 1).Value
            row4 = row4 + 1
        Else
            Sheet5.Cells(row5, col5).Value = Sheet1.Cells(row, col).Value
            Sheet5.Cells(row5, col5 + 1).Value = Sheet1.Cells(row, col + 1).Value
            row5 = row5 + 1
        End If
    Next
End Sub
 
Upvote 0
I can't get my head around this. help

I pasted it in but it is not running from what I see.

This is a diferent way of do this then what I am used doing. I can follow loops but arrays, I still working on.
 
Upvote 0
I cant tell if Chris' code is close, right on the money, or not close at all 'cause I still don't know the answers to the questions above.
I assume that you/your users are making the entries in sheet2 column D. (yes?)
In that case the code provided should go into the sheet2 code module to see if it does what you're after.
 
Upvote 0
I was trying to add that into code I had. I 'll put it into the worksheet and try again from there.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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