VBA Match Cell Range to Worksheet Name and copy/paste to respective worksheet

Hello,

I have a tab named "Data" and 2 other tabs named Bill and Jim. Column A in the Data tab contains Names of employees (Jim and Bill) and all employees have a corresponding tab. What I am trying to do is match the names in column A of the "data" tab with the corresponding sheets that have the same names and then paste the names into their respective sheets in column A. Everytime I populate I am trying to get it to paste in the next empty row. In the "data" tab there might be 5 entries for Jim and 3 for Bill and I am trying to get the 5 Jim entries to paste in the Jim tab and the 3 Bill entries to paste in the Bill tab. Any help on this would be greatly appreciated as I have been struggling with it for the past few hours.

Thanks so much for your help in advance.
The code below somewhat works but i cannot include the entire column.

Public Sub sheettosheet()
Dim wksht As Worksheet
For Each wksht In Sheets
If ActiveSheet.EntireColumn("A3").Value = wksht.Name Then
ActiveSheet.Range("B3:U3").CurrentRegion.Copy Destination:=wksht.Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next
End Sub

It will be easier if you attach a sample workbook so we can provide you a solution easily.

Originally Posted by rlsublime

Public Sub sheettosheet()
Dim wksht As Worksheet
For Each wksht In Sheets
If ActiveSheet.EntireColumn("A3").Value = wksht.Name Then
ActiveSheet.Range("B3:U3").CurrentRegion.Copy Destination:=wksht.Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next
End Sub

There is no EntireColumn("A3")
There is no value to an EntireColumn.
CurrentRegion picks up ALL cells that are surrounded by empty cells. Assuming that you have data in rows 3 to 50, Range("B3:U3").CurrentRegion would pick up "A3:U52".
Try this: (I did this on the fly without testing, so back up).

Please Login or Register  to view this content.