Solved Copy Row if Cell is equal to text

April 21, 2010 at 12:44:15
Specs: Windows XP
Hi. I am basically new with coding and mostly i just edit my recorded macros. I need help with a particular worksheet.

I have a worksheet where column F for example contains values equal to "A", "B", and "C" using data validation. How can I automatically copy the contents of the row where Column F is equal to "B" then copy it to the next sheet.

Basically what I am trying to do is once, I select the value "B" from the validation drop down list under column F, that active row will be transferred to the next available row in the next sheet

See More: Copy Row if Cell is equal to text

Report •

April 21, 2010 at 20:19:48
✔ Best Answer
This WorksheetChange macro should get you started.

You didn't tell us which sheet is the "next sheet" so I assumed you are changing Sheet 1 and copying the rows to Sheet 2.

Right Click the sheet tab for Sheet 1, choose View Code and paste this into the window that opens.

Since Macros can not be undone, I suggest you test this code in a backup copy of your workbook in case things go horribly wrong.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRow As Integer
'Determine if change was to Column F (6)
 If Target.Column = 6 Then
'If Yes, Determine if cell = B
  If Target.Value = "B" Then
'If Yes, find next empty row in Sheet 2
   nxtRow = Sheets(2).Range("F" & Rows.Count).End(xlUp).Row + 1
'Copy changed row and paste into Sheet 2
    Target.EntireRow.Copy _
     Destination:=Sheets(2).Range("A" & nxtRow)
  End If
 End If
End Sub

Report •

April 22, 2010 at 13:01:17
Thanks a lot!

Code is working... I'll be happy with it by how it works now. I also already went ahead and tested with some of the values,. As you may have guessed the values "A", "B" and "C" represent other items.

Though, would it be possible that if after the row had been copy-pasted to the next sheet, then after a few days I went back and changed the value again under column F (example, i changed it from "B" back to "A"), would it then be possible for the previously copied row to be removed from the second sheet?

Thanks for getting me started though! Appreciate it a lot!

Report •

April 22, 2010 at 13:46:40
What you are asking for is possible, but a bit (possibly a lot) more complicated.

Once the code has completed its task, it knows nothing about your spreadsheet. It merely copies the rows based on the value in F and pastes it in the next empty row on Sheet 2.

In order to delete a specific row, it would need some way to find that row.

For example, it would need to search Sheet 2 based on some criteria from the changed row in Sheet 1 to figure out which row to delete. It could be as simple as a search on Column A - if, and only if, there are only unique values in Column A (or B or C or whatever).

In other words, if Column A of the changed row contains "Bubba", and there is only one instance of "Bubba" in Column A of Sheet 2, then deleting that line can be done with one search. However, if "Bubba" appears more than once, then we need to search on something else until we can narrow it down to which row to delete.

We might have to say "Find Bubba in Column A, if there is more than one, then find Red in Column B. If there is more than one Bubba Red combination, then find 6 in Column C, etc."

Does that make sense?

Report •

Related Solutions

April 22, 2010 at 14:43:56
Yep. I understand... and you're right it is a lot more complicated.

I already replicated your code and is able to make it work wherein if Column F contains "C", it goes to the 3rd sheet.

I think I will be happy with what I have so far. This is just for tracking invoice statuses and I can't find details on my sheet that can be unique per entry as some invoices are re-used. But, I guess I shouldn't bee to lazy to manually delete one or two items...

Thank you very much for your help.

Report •

April 23, 2010 at 13:04:15
Hi. Just a follow up question.

I am not looking to remove the entries in sheet 2 when column F in sheet 1 changes. But everytime I delete an entry in sheet 1, I get a type mismatch error... How can i fix this?

Report •

April 23, 2010 at 21:19:45
re: I already replicated your code and is able to make it work wherein if Column F contains "C", it goes to the 3rd sheet.

It sounds like you modified my code. Perhaps you should post the code that you are now using.

I don't get any errors if I delete something when the code I suggested is used.

Report •

April 28, 2010 at 06:52:47
Basically I changed the if function to select case... i retained your function to lookup the next available row in the next sheet. 'Coz that is what I had difficulty with, identifying ranges and setting targets.. Here is what I have

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRow As Integer

'Determine if change was to Column J (10)
If Target.Column = 10 Then

'Select the Case in question
Select Case Target.Value
Case "Pending": nxtRow = Sheets(2).Range("J" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy Sheets(2).Range("A" & nxtRow)

Case "Assigned": nxtRow = Sheets(3).Range("J" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy Sheets(3).Range("A" & nxtRow)

End Select
End If
End Sub

Also, I am having a problem when I want to change the range. I am trying to modify it to copy up to column J only instead of copying the entire row. I figure it is changing the Target.EntireRow to Target.Address but I am having problems trying to identify it.

Report •

April 28, 2010 at 08:41:24
Even with your version of the code, I'm not getting any errors when I delete something in Sheet 1.

I guess I would need to know exactly what steps you are taking to create the error.

As far as just copying A:J try this:

Range("A" & Target.Row & ":J" & Target.Row).Copy

This is the basic syntax for the Range property:


You can "build" the argument by using variables:

Range("A" & myNum & ":J" & myOtherNum)

Since the Target "variable" knows everything about itself, Target.Row is the Row number of the Target.

Report •

April 28, 2010 at 10:01:24
The error happens everytime i select multiple cells in column J then press delete. I guess the event doesn't recognize that I am not solely selecting column J and should exit the macro

Report •

April 28, 2010 at 10:43:03
Try adding this at the beginning of the code:

If Target.Cells.Count > 1 Then Exit Sub

Report •

April 29, 2010 at 12:38:25
Wow! That works... if i understand it correctly it will stop running the macro if multiple cells are selected right???

Thanks for helping!

Report •

April 29, 2010 at 13:19:25
Well, I guess it's a case of semantics.

The macro doesn't "stop running", it simply follows another path to completion.

As soon as a change to the worksheet is made, the macro runs. The first line tells it to count how many cells were changed. If that count is >1, then the code executes the TRUE portion of the If statement. The instruction found there is to Exit the routine.

So, one could say that the code runs to completion as opposed to stopping, it just takes a very short path.

This is somewhat important since there actually is a Stop instruction in VBA which acts very differently than Exit Sub.

Report •

April 30, 2010 at 08:51:56
I see. So basically it fulfills a condition set which results to completing the function as opposed to stopping everything.

I somehow understand now. It may be a case of semantics, but if i want to learn, i guess i have to pay more attention to detail. Thanks again DerbyDad

Report •

April 30, 2010 at 09:54:26
re: "i guess i have to pay more attention to detail"

True...which is why I'll mention something else:

"which results to completing the function as opposed to stopping everything."

The code I sent is not a function, it is a subroutine.

The main difference is that a VBA function returns a value, but a VBA subroutine does not.

VBA can be used to create User Defined Functions (UDF) to perform tasks and return values that the built in Excel functions can't. The function name is placed in a cell just like a built in function, but it runs the VBA code associated with it to return the value.

You can recognize a UDF because it starts with Function <name> instead of Sub <name>.

For example, Excel has no built in function to Sum a range based on if the cells in the range are bold or not. However, a UDF could be constructed to do just that:

Function SumBold(ByVal Target As Range)
  For Each tCell In Target
    If tCell.Font.Bold = True Then _
     bldSum = bldSum + tCell
   SumBold = bldSum
End Function

If you enter =SumBold(A1:A10) in a cell, it will sum only those cells in A1:A10 where the font is set to bold.

Some fine examples of UDF's can be found here:

Start with the top link to "Custom Functions Introduction"

Report •

May 3, 2010 at 11:14:01
i see...

thanks for the link... I don't think i'd be able to master everything in one go... but at least I am learning. Thanks man!

Report •

May 3, 2010 at 13:20:52
i was wondering if you can help me with one final modification.

Just few modification if possible?

1.) What do i change if i want it to copy to a new workbook


2.) Will using select case also be possible if i want to copy an entire row if, for example, Column M has an entry (nonblanks). It doesn't have to be automatic, I can assign a button for this.

Thanks in advance!!!

Report •

May 3, 2010 at 16:04:15
1.) What do i change if i want it to copy to a new workbook

That depends on what you're asking.

A new workbook every time the criteria is met?
A new workbook for Pending and a new workbook Assigned?
You need to be more specific.

2.) Will using select case also be possible if i want to copy an entire row if, for example, Column M has an entry (nonblanks).

Just about anything is possible, but once again you need to more specific.

Do you want to copy the entire when M has an entry and column J meets your other criteria? Or when it doesn't meet your other criteria? Or exactly when?

Since the VBA code has to follow a logical sequence, you have to tell it when to do everything.

If This is true, then do That. If That is true, then do This. If This AND (OR?) That is true then do something else.

Report •

May 4, 2010 at 14:35:39
I see...

Alright I'll try to look for answers first on the website that you showed me. If I can't find answers, i hope you wouldn't mind, but I'd be back here.

Report •

Ask Question