Copy Row from Sheet1 to Sheet2 or Sheet3

November 24, 2011 at 15:05:18
Specs: Windows 7
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 B

Anyone? Thanks


See More: Copy Row from Sheet1 to Sheet2 or Sheet3

Report •


#1
November 24, 2011 at 16:12:07
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 If

Just 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.


Report •

#2
November 24, 2011 at 17:15:44
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 Sub

Thanks


Report •

#3
November 24, 2011 at 18:41:09
Please click on the blue line at 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.


Report •

Related Solutions

#4
November 25, 2011 at 13:10:03
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 Sub

What 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


Report •

#5
November 25, 2011 at 15:14:17
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.


Report •

Ask Question