What do you want to merge ?, reading your message I understand you already have all data in Sheet 2, sheet 1 only contain manager name in addition but you don't want to have this in Merged sheet, so I have hard time to understand what do you want to put extra in merged sheet . Can you give an example so that it is clear
Sorry I was not that clear, here is an example of what I would like to have happen.
Sheet1
Name ID # Badge # Grade Manager
John Doe 1 123456 98765 S3 EM 1
John Doe 2 246810 87654 S1 EM 2
John Doe 3 370164 76543 T5 EM 3
John Doe 4 493518 65432 T2 EM 4
John Doe 5 616872 54321 O5 EM 5
John Doe 6 740226 43210 T3 EM 6
John Doe 7 863580 32099 S1 EM 7
John Doe 8 986934 20988 T1 EM 8
John Doe 9 111028 98774 S4 EM 9
John Doe 10 123364 11234 T2 EM 10
Sheet2
Badge # Name CCC Project Total Hours
87654 John Doe 2 GHWJKD Project 1 40
87654 John Doe 2 GHWJKD Project 2 80
87654 John Doe 2 GHWJKD Project 3 40
98765 John Doe 1 QDSFGH Project 5 80
98765 John Doe 1 QDSFGH Project 6 80
76543 John Doe 3 HEGTBN Project 20 160
65432 John Doe 4 JSHTBA Project 1 120
65432 John Doe 4 JSHTBA Project 4 40
54321 John Doe 5 SJFHAF Project 2 160
11234 John Doe 10 OPITWL Project 1 160
98774 John Doe 9 EYFHKD Project 7 160
20988 John Doe 8 VMANS Project 4 40
20988 John Doe 8 VMANS Project 5 30
20988 John Doe 8 VMANS Project 20 20
20988 John Doe 8 VMANS Project 3 30
20988 John Doe 8 VMANS Project 6 40
32099 John Doe 7 ASKJNR Project 1 160
43210 John Doe 6 MDNFJ Project 2 60
43210 John Doe 6 MDNFJ Project 4 100
Created Final Sheet
Badge # Name ID # CCC Grade Manager Project Total Hours
98765 John Doe 1 123456 QDSFGH S3 EM 1 Project 5 80
98765 John Doe 1 123456 QDSFGH S3 EM 1 Project 6 80
87654 John Doe 2 246810 GHWJKD S1 EM 2 Project 1 40
87654 John Doe 2 246810 GHWJKD S1 EM 2 Project 2 80
87654 John Doe 2 246810 GHWJKD S1 EM 2 Project 3 40
76543 John Doe 3 370164 HEGTBN T5 EM 3 Project 20 160
65432 John Doe 4 493518 JSHTBA T2 EM 4 Project 1 120
65432 John Doe 4 493518 JSHTBA T2 EM 4 Project 4 40
54321 John Doe 5 616872 SJFHAF O5 EM 5 Project 2 160
43210 John Doe 6 740226 MDNFJ T3 EM 6 Project 2 60
43210 John Doe 6 740226 MDNFJ T3 EM 6 Project 4 100
32099 John Doe 7 863580 ASKJNR S1 EM 7 Project 1 160
20988 John Doe 8 986934 VMANS T1 EM 8 Project 4 40
20988 John Doe 8 986934 VMANS T1 EM 8 Project 5 30
20988 John Doe 8 986934 VMANS T1 EM 8 Project 20 20
20988 John Doe 8 986934 VMANS T1 EM 8 Project 3 30
20988 John Doe 8 986934 VMANS T1 EM 8 Project 6 40
98774 John Doe 9 111028 EYFHKD S4 EM 9 Project 7 160
11234 John Doe 10 123364 OPITWL T2 EM 10 Project 1 160
So far I have used
Code:
Sub TestGridUpdate()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim TestGridFound As Boolean
Dim r As Range
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")
TestGridFound = False
For Each ws In Worksheets
If ws.Name = "TestGrid" Then TestGridFound = True
Next
If TestGridFound Then
Set ws3 = ThisWorkbook.Worksheets("TestGrid")
ws3.Cells.Clear
Else
Set ws3 = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
ws3.Name = "TestGrid"
End If
ws3.Range(ws1.UsedRange.Address).Value = ws1.UsedRange.Value
For Each r In ws3.UsedRange.Rows
ID = r.Cells(, 1).Value
iRow = Application.Match(ID, ws2.UsedRange.Columns(2), 0)
If Not IsError(iRow) Then ws2.Range("A" & iRow & ":U" & iRow).Copy ws3.Range("G" & r.Row)
Next
End Sub
Unfortunately it only returns the first project it finds though many people may have more than 1.
Please let me know if I need to clear anything up!
Thanks!
