Copy data to other sheet based on reference macro

[hgufrin]

Hi,
I was wondering if anyone can help me out with code that will read the data in column A, copy data in columns B&C, and paste it to another already existing sheet in the same workbook. Column A contains the sheet name it will copy it to. Sure hope you can help.

I must add that when it it copies it to the appropriate sheet... it will pasted it in the next available row. As I update this same workbook daily...
Thanks for your time.

Book2
	A	B	C	D
1	TabName	SalesDate	SalesAmount
2	Mustang	12/2/2012	$35,000
3	Camaro	5/1/2013	$32,000
4	Pinto	5/2/2013	$150
Sheet1

 

[ZAX]

Hope this will help you

Sub CopyCells2Sheets()
For Each Cell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    Range(Cells(Cell.Row, 1), Cells(Cell.Row, Cells(1, Columns.Count).End(xlToLeft).Column)).Copy Sheets(Cell.Value).Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1)
Next
End Sub

ZAX

 

[hgufrin]

Unfortunately it didn't do anything.

 

[hgufrin]

I have this but cant figure out how to copy/paste only cell B&C. You can see that down below it is copying the ENTIRE row which is not what i want. I would like ONLY B&C copied/pasted.
Thanks for your help ZAX

Dim c As Range
Dim WsName As String
For Each c In Range("a2:a10")
Select Case c.Value
Case "Mustang": WsName = "Mustang"
Case "Camaro": WsName = "Camaro"
Case "Pinto": WsName = "Pinto"
End Select
c.EntireRow.Copy Worksheets(WsName).Range("A" & Rows.Count).End(xlUp).Offset(1)
Next c

 

[ZAX]

I have this but cant figure out how to copy/paste only cell B&C. You can see that down below it is copying the ENTIRE row which is not what i want. I would like ONLY B&C copied/pasted.
Thanks for your help ZAX

Dim c As Range
Dim WsName As String
For Each c In Range("a2:a10")
Select Case c.Value
Case "Mustang": WsName = "Mustang"
Case "Camaro": WsName = "Camaro"
Case "Pinto": WsName = "Pinto"
End Select
c.EntireRow.Copy Worksheets(WsName).Range("A" & Rows.Count).End(xlUp).Offset(1)
Next c

Sorry for not correcting YOUR code,but this one is better;your code only copies to sheets mustang and camaro and pinto,while my code copies B&C to whatever sheet name in column A.
This one is amazing!

Sub CopyCells2Sheets()
For Each Cell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    Range(Cells(Cell.Row, 2), Cells(Cell.Row, 3)).Copy Sheets(Cell.Value).Range("A" & Sheets(Cell.Value).Cells(Rows.Count, 1).End(xlUp).Row + 1)
Next
End Sub

ZAX

 

[hgufrin]

Thanks Zax. Let's start over. Assume I am newbie. Which I am.

When I run your code - I get an error... and it highlights your code shown below. Also, it is not pasting anything in the destination cells.

You are right - in saying, ",while my code copies B&C to whatever sheet name in column A". This is exactly what I want it to do but just can't figure how to get your code to work.


Range(Cells(Cell.Row, 2), Cells(Cell.Row, 3)).Copy Sheets(Cell.Value).Range("A" & Sheets(Cell.Value).Cells(Rows.Count, 1).End(xlUp).Row + 1)

 

[ZAX]

I tried it so many times,the error occurs in this line of code when there are no sheets with the name mentioned in the cell!
Use the following code to prevent having an error and to prove that I'm right:

Sub CopyCells2Sheets()
For Each Cell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    S = 0
    i = 0
    For ShC = 1 To Worksheets.Count
        S = S + 1
        If Sheets(ShC).Name = Cell.Value Then
           i = i + 1
        End If
        If S = Worksheets.Count And i = 0 Then
           MsgBox "There are no sheets with the name typed in " & Cell.Address
           GoTo 1
        End If
    Next
    Range(Cells(Cell.Row, 2), Cells(Cell.Row, 3)).Copy Sheets(Cell.Value).Range("A" & Sheets(Cell.Value).Cells(Rows.Count, 1).End(xlUp).Row + 1)
1 Next
End Sub

Zax

 

[hgufrin]

ZAX,

This is awesome and you are CORRECT. Thanks so much for your time. This works perfect.

 

[ZAX]

ZAX,

This is awesome and you are CORRECT. Thanks so much for your time. This works perfect.

You're welcome!

 

[ZAX]

ZAX,

This is awesome and you are CORRECT. Thanks so much for your time. This works perfect.

You're welcome!