Solved Copy Row if cell is equal to name

July 6, 2016 at 13:16:28
Specs: Windows 7
In Excel, I want for the specific row to be copied to a separate worksheet, when a cell in column J, starting at row 4 has a name in it, such as Mike.

Ex: Cell J4 has the text "mike" in Sheet1, therefore copy A4:J4 on Sheet1 to Sheet2.

message edited by toraye


See More: Copy Row if cell is equal to name

Report •

✔ Best Answer
July 7, 2016 at 09:46:36
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRw As Long
'Determine if Mike was entered in Column J
  If Target.Column = 10 Then
    If Target = "Mike" Then
'If Yes, determine next empty Row in Sheet 2 and copy A:J
      nxtRw = Sheets(2).Range("J" & Rows.Count).End(xlUp).Row + 1
      Range("A" & Target.Row & ":J" & Target.Row).Copy _
         Sheets(2).Range("A" & nxtRw)
     End If
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
July 7, 2016 at 00:01:01
toraye,

When do you want the copy to occur?

Should the copy occur as soon as "Mike" is entered in the cell or do you want to run a macro that loops through existing data, copying the row each time Mike is found in Column J?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 7, 2016 at 07:15:21
Mike will appear multiple times in column J, as well as other names. So I want the row to copy when the name appears into the respective sheet.

i.e.: "Mike" appears in cell J5, copy entire row from A5:J5 to "Mike" worksheet.


Report •

#3
July 7, 2016 at 08:10:27
You have not answered my question. Let me try again.

There are (at least) 2 options for copying the row when Mike "appears in cell J5".

1 - There are event macros that can copy the row as soon as the user types "Mike" into J5. As soon as the user moves away from the cell either by hitting enter, using the arrow keys or clicking with the mouse, the code will check to see if the cell contains Mike. If it does, the code will copy the desired range.

2 - There are "manual" macros which you can run that will start in Row 1 and check each cell in Column J to see what it contains. Each time it finds "Mike", it will copy that row and then continue checking the column until it reaches the bottom of the data.

I am asking you to tell us which method you want to use: The immediate method that happens each and every time Mike is entered into Column J or the "one time" method that scans the existing data in Column J and copies the ranges whenever Mike is found.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 7, 2016 at 08:23:36
Sorry! I would love for it to do it automatically (the first option)

Report •

#5
July 7, 2016 at 09:46:36
✔ Best Answer
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRw As Long
'Determine if Mike was entered in Column J
  If Target.Column = 10 Then
    If Target = "Mike" Then
'If Yes, determine next empty Row in Sheet 2 and copy A:J
      nxtRw = Sheets(2).Range("J" & Rows.Count).End(xlUp).Row + 1
      Range("A" & Target.Row & ":J" & Target.Row).Copy _
         Sheets(2).Range("A" & nxtRw)
     End If
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
July 7, 2016 at 09:57:09
And if I want to do it for multiple people for other spreadsheets, would I use this same formula, but change the name & Sheet number?

Report •

#7
July 7, 2016 at 10:29:51
also, i know i add that formula to visual basic but how exactly do i do that?

Report •

Ask Question