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]

 

[mumps]

Hi Strephen, Try:

Sub CopyRows()
    Dim bottomD As Integer
    bottomD = Range("D" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Dim ws As Worksheet
    For Each c In Sheets("all").Range("D2:D" & bottomD)
        For Each ws In Sheets
            ws.Activate
            If ws.Name = c Then
                c.EntireRow.Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Next ws
    Next c
End Sub

 

[stephenp1983]

Thanks for the help on this. I'd like to expand on this and how to format the macro to copy from three worksheets instead of the single one.I've tried doing this with an array but the syntax isn't quite right


For example in the currentl macro this line reads For Each c In Sheets("worksheet1").Range("K2:K" & bottomK). I've added the following to go through through the additional sheets - For Each c In Sheets(Array("worksheet1", "worksheet2").Range("H2:H" & bottomH))


I'm guessing the issue is with the .range part that follows. Any suggestions would be appreciated.

 

[mumps]

The macro I suggested copies data from the "all" sheet to each sheet that matches the 'owner' name. Do you want to copy data from the 3 sheets to each sheet that matches the 'owner' name? Please clarify.

 

[stephenp1983]

Yes that is correct instead of copying from the all tab I'd like it to look in an additional two tabs that contain the owner colomn in the same position and copy those rows to the matching named owner worksheets. Basically I've split the all into three more defined categories thanks for your help

 

[mumps]

I have to go out for an appointment right now so I will get back to.

 

[stephenp1983]

I have to go out for an appointment right now so I will get back to.

Ok no rush thanks for responding back so quickly

 

[mumps]

Hi Stephen. Sorry for the delay. See if this works for you. Change the sheet names to suit your needs.

Sub CopyRows()
    Dim bottomD As Integer
    Dim c As Range
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    For Each ws1 In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        ws.Activate
        bottomD = Range("D" & Rows.Count).End(xlUp).Row
        For Each c In Range("D2:D" & bottomD)
            For Each ws2 In Sheets
                If ws2.Name = c Then
                    ws2.Activate
                    c.EntireRow.Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                End If
            Next ws2
        Next c
    Next ws1
End Sub

 

[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

 

[stephenp1983]

Also a side related question... Just want to know if its possible to copy the output to a named table on a worksheet instead of the worksheet itself. Not worried about doing that now but wanted to know if it could be done and expand the table accordingly