Copy to another worksheet

[Chente28]

I need help creating a macro that will copy certain cells from one worksheet and paste them to another. In the example I included I marked the cells i need to copy with blue.

I get this list every week. The problem I have is that sometimes the number of people in each group varies. Like this week for example there are 14 people in GROUP1 and next week there will be 13. So my macro wouldn't work each time. Can anyone help me?

Thanks,

Alex

[jindon]

Hi
try

Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim r As Range, ff As String
Set ws1 = Sheets("sheet1")
Set ws2 = Sheets("sheet2")
Set r = ws1.Columns("a").Find(1, , , xlWhole)
    If Not r Is Nothing Then
        ff = r.Address
    Do
        With ws1.Range(r, r.End(xlDown)).Offset(, 1)
            Union(.Resize(, 2), .Offset(, 6)).Copy
            ws2.Range("a" & Rows.Count).End(xlUp).Offset(1). _
            PasteSpecial xlPasteValues
        End With
        Set r = ws1.Columns("a").FindNext(r)
    Loop Until ff = r.Address
    End If
End Sub

[Chente28]

jindon,

Thanks It's perfect.