Moving Data Between Worksheets with Match?

mharper90

Board Regular
Joined
May 28, 2013
Messages
117
Office Version
  1. 365
Platform
  1. MacOS
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.)



Code:
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Anyone have at least something to help get me rolling in the right direction? I don't think my first attempt is on track...
 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top