Moving Copy Process down to next line

First, using Excel 2010.

Second, the raw data I'm looking at is similar to this:

0001 AUT123
0002 LIF654 LIF987
0003 AUT985 LIF456

The output I want is for all LIF elements to be reported in a nice table like this:
0002 LIF654
0002 LIF987
0003 LIF456

As you can see, the first one was skipped because it does not have LIF. The second is supposed to report on 2 different rows, splitting the two LIF values. The third reports the 2nd element....

Currently, this is the code I have in my macro:

Please Login or Register  to view this content.

What it is doing is that it picks up the first LIF for 0002 and puts it in BUT THEN it overwrites that with the second LIF for 0002 - it needs to shift down to the next row and copy it over but its not working.

Help :/

Can you paste a workbook of your data? I'm unclear as to how it's arranged. For instance, in your raw data example, you show
0001 AUT123
0002 LIF654 LIF987
0003 AUT985 LIF456
which makes it appear that the data is in adjacent cells, but your macro starts in column A, then looks for a cell in column 10, which indicates it's not adjacent. Then there's the whole "Copy Remainder of Customer information" . We need to be able to see how the data is all laid out; a "before" and "after" spreadsheet would help.

The cells are NOT adjacent - there are identifiers that need to be copied over along with the cells I'm searching for. I can dummy something up but I cannot post my actual data.

I think this might do what you need, but it operates completely differently from your existing macro. To the original data I added a couple of test lines to isolate LIF in one or the other columns. I suggest you step through the code (hitting F8) to see what it's doing and ensure it's doing what you want.

Please Login or Register  to view this content.