Move data to the right tab

outlawspeeder

Board Regular
Joined
Jan 17, 2009
Messages
223
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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

Chris Bode

Board Regular
Joined
Jan 25, 2009
Messages
103
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

outlawspeeder

Board Regular
Joined
Jan 17, 2009
Messages
223
Office Version
  1. 2019
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

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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

outlawspeeder

Board Regular
Joined
Jan 17, 2009
Messages
223
Office Version
  1. 2019
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,195,670
Messages
6,011,074
Members
441,581
Latest member
rp4717

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
Top