Macro To copy and paste data from a list to corresponding worksheet and cells

[BigDawg15]

Hello,

In the attached workbook, I am looking for VBA that would copy the data from column E on the Activities wksheet and paste as values matching Column B (Date) and Column G (name) to the appropriate named wksheet (Group) and matching date and name.

The complete list of Groups and Names are in columns I & J for reference.

I hope I have explained it sufficiently.

Thank you in advance for any assistance,

BigDawg15

2017 Work Activity Schedule v1 - Copy.xlsm

[mdmackillop]

Option Explicit


Sub Test()
    Dim r As Range, cel As Range, Ws As Worksheet
    Dim Rw&, Col&, Grp$, Nm$
    With Sheets("Activities")
        Set r = Range(.Cells(2, 5), .Cells(Rows.Count, 5).End(xlUp))
    End With
    For Each cel In r
        If Len(cel) > 3 Then
        Grp = Replace(cel.Offset(, 1), "'", "")
        Set Ws = Worksheets(Grp)
        Col = Ws.Rows(1).Find(cel.Offset(, -3)).Column
        Nm = Replace(cel.Offset(, 2), "'", "")
        Rw = Ws.Columns(2).Cells.Find(Nm, lookat:=xlWhole).Row
        Ws.Cells(Rw, Col).Value = cel.Value
        Ws.Cells(Rw, Col).Interior.ColorIndex = 8  ' Can be deleted
        End If
    Next cel
End Sub

[BigDawg15]

mdmackillop,

Absolutely awesome!!

Thank you so much for your help.

Have a great day,

Mike