I have a sheet where I would like to copy a row to either sheet2 or sheet3 based on the content of one of the cells in the row. I found this macro and it works for one Sheet but i can not get it to copy to the Sheet3. "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"Here is a sample of the data I am working with:

9/13/2008 Belle Tire 99 AAA 3 4 B

9/14/2008 Rangers 99 AAA 3 4 A

9/14/2008 Americans 99 AAA 4 1 BAnyone? Thanks

This is the section that copies the data to Sheet2 if the value in Column F is "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 IfJust replicate that section below itself in the macro and change the "B" to whatever value you are using for Sheet(3) and change the "Sheets(2)" to "Sheets(3)".

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

So this is what I have now but it is not working, 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 = A

If Target.Value = "A" 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)

If Target.Value = "B" Then

'If Yes, find next empty row in Sheet 3

nxtRow = Sheets(3).Range("F" & Rows.Count).End(xlUp).Row + 1

'Copy changed row and paste into Sheet 3

Target.EntireRow.Copy _

Destination:=Sheets(3).Range("A" & nxtRow)

End If

End If

End If

End SubThanks

Please click on the blue lineat the end of this post and read the instructions on how to post code in this forum, then repost your code.BTW..."it is not working" doesn't mean much to those of us not sitting in front of your computer. Just a little more detail might help.

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

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 = A If Target.Value = "A" 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) If Target.Value = "B" Then 'If Yes, find next empty row in Sheet 3 nxtRow = Sheets(3).Range("F" & Rows.Count).End(xlUp).Row + 1 'Copy changed row and paste into Sheet 3 Target.EntireRow.Copy _ Destination:=Sheets(3).Range("A" & nxtRow) End If End If End If End SubWhat I mean by not working is that it is not copying and transferring the data from Sheet1 to Sheet2 or Sheet3. Here is a sample of the data im working with.

9/10/2008 Sting 7 2 A 9/13/2008 Belle Tire 3 4 B 9/14/2008 Rangers 3 4 A 9/14/2008 Americans 4 1 b 9/14/2008 Hurricanes 4 3 a

First, without Column letters or Row numbers associated with your sample data I can't tell what data you have where, therefore I can't tell if the code will work with your data or not. Second, you have not correctly replicated the section of code that I pointed out.

Go back and follow your IF's and End If's. You have an End If out of place.

With the A's and B's entered into the correct column, and the End If in the right location, the code does exactly what it should do:

Copies lines where an A is entered into Column F to Sheet 2 and copies lines where a B is entered in Column F to Sheet 3.

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

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History