Moving Data Between Worksheets with Match?

[mharper90]

I have an interesting data set that I'm trying to move data between. I've taken a partial swing at this and am coming up short. I keep getting errors on my match formula, and therefore haven't pieced the rest of the code together. Take a look at my attempt if you care, or if you have a solution to the below scenario, I'd greatly appreciate the help!

There is a Main Data worksheet (ws1) that contains a roster of names in column B and serial numbers assigned to each person in column C. Some people have more than one serial number, so their name appears multiple times, but the first instance always has "NO" in column A. All of the repeats of an individual name have "DPL" in column A. In columns I:P, there is data that is tied to each person/serial number. I need a macro that will translate the data in columns I:P to 8 different worksheets named "P1 Figure 2-2" through "P8 Figure 2-2", putting the data into columns G:N. The way the data works in real life is very confusing, so I'm not even going to try to explain it...but just know this: If a name in column B of ws1 has a "NO" in column A, than "NO" is the unique identifier for that row. If a name in column B of ws1 has a "DPL" in column A, than the serial number in column C is the unique identifier for that row. Btw, columns A, B, and C contain the same data types on all worksheets, the only difference is that data from I:P on ws1 needs to go to G:N on the other 8 worksheets.

Essentially, I need the macro to search all of the names in column B of ws1. If it has a "NO" in column A, then it should find the same name that also has "NO" in column A on all of the 8 other worksheets, then translate the data from ws1 columns I:P to columns G:N of the other 8 worksheets. Note, not all of the 8 other worksheets will have a matching name with "NO", but that's ok, it should just keep moving on.

Then, if a name in column B of ws1 has "DPL" in column A, then it should find the same name that also has the same value in column C on all of the 8 other worksheets, then translate the data from ws1 columns I:P to columns G:N of the other 8 worksheets. (Same data translation as above, just a different matching value. Also, not every sheet will have a match, again.)

Sub recordRslts()

Dim ws1     As Worksheet: Set ws1 = ThisWorkbook.Sheets("Main Data")
Dim Lr     As Long
Dim x     As Long
Dim c     As Range
Dim a     As Long

For x = 1 To 8

Lr = ThisWorkbook.Sheets("P" & x & " Figure 2-2").Range("A" & Rows.Count).End(xlUp).Row

     For Each c In ThisWorkbook.Sheets("P" & x & " Figure 2-2").Range("B3:B" & Lr)
          If c.Offset(, -1).Value = "NO" Then
               a = Application.WorksheetFunction.Match(c.Value, ws1.Range("B7:B" & Lr), 0)   '<--- This Part Doesn't Work...
               c.Offset(, 5).Value = ws1.Range("1" & a)
          End If
     Next c

Next x

End Sub

 

[mharper90]

Anyone have at least something to help get me rolling in the right direction? I don't think my first attempt is on track...