Macro to match cell value to worksheet name and copy row data

[stephenp1983]

I'm trying to create a macro that will look at the values in a column on worksheet1, and then copy the values from that row to worksheets whose name matches the value of the column. An example is below:

Main worksheet is named all. It has four columns (Name, Address, Location, Owner) The owner column has multiple values, each appearing on multiple rows. I have addditional worksheets created and named after all the possible values in the owner column (team 1, team 2, team 3). I would like the macro to copy the data in each row to the worksheet tab that matches the owner column value.

I found this thread, but it appears to be a little different from what I'm trying to accomplish -
http://www.mrexcel.com/forum/excel-...ons-match-cell-value-worksheet-name-copy.html

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Address[/TD]
[TD]Location[/TD]
[TD]Owner[/TD]
[/TR]
[TR]
[TD]server1[/TD]
[TD]random address[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="width: 59"]office 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]team1[/TD]
[/TR]
[TR]
[TD]server2[/TD]
[TD]random address[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="width: 59"]office 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]team2[/TD]
[/TR]
[TR]
[TD]server3[/TD]
[TD]random address[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="width: 59"]office 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]team3[/TD]
[/TR]
[TR]
[TD]server4[/TD]
[TD]random address[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="width: 59"]office 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]team3[/TD]
[/TR]
[TR]
[TD]server5[/TD]
[TD]random address[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="width: 59"]office 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]team3[/TD]
[/TR]
[TR]
[TD]server6[/TD]
[TD]random address[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="width: 59"]office 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]team1[/TD]
[/TR]
[TR]
[TD]server7[/TD]
[TD]random address[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="width: 59"]office 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]team1[/TD]
[/TR]
</tbody>[/TABLE]

 

[stephenp1983]

Mumps, I've add the changes and now get an immediate run time error 424 object required. It seems to be pointing to the ws.activiate line. Thanks again for all your help

I see to have found the issue here, should have noticed it before - had to add 1 to ws1.activiate since there are two instances now.

 

[mumps]

Is everything working the way you wanted now?

 

[stephenp1983]

Yes all is working now. Thanks gain for all of your help

 

[mumps]

My pleasure.