PDA

View Full Version : [SOLVED] Macro To copy and paste data from a list to corresponding worksheet and cells



BigDawg15
08-24-2017, 08:03 AM
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

20167

mdmackillop
08-24-2017, 12:09 PM
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
08-24-2017, 12:37 PM
mdmackillop,

Absolutely awesome!!

Thank you so much for your help.

Have a great day,

Mike