Macro to search a string, insert a new row, copy existent data, copy matching string

Hello,

I need to create a macro who can search for a partial string (ex: TAR-WKS or TAR_LCD) on the H,J,I and K columns and if a string with that name it's found it should:

1. Insert a new line below the line where the string it's found.
2. Copy the content of the A,B,C,D and E columns from the line where the search string was found and paste them on the new line inserted at point 1, using same location - A on A, B on B, .., E on E.
3. CUT the found string along with the rest of the text (ex: TAR-WKS152 or TAR_LCD668) to the new line created as follows:

if it's TAR-WKS"a number" it should be pasted on the new line, column F.
if it's TAR_LCD"a number" it should be pasted on the new line, column G.


test environment excel file attached.
test_file.xlsx

Hi,

I was unable to open the spreadsheet you attached, but I think I was able to make a sample one and write a macro to do what you were looking for based on the description. The code to do what you listed is shown below. I have attached the *.xls file that includes the macro. Press "<CTRL> a" to run the macro on the data shown on Sheet1. Hit "<CTRL> z" to reset the data on Sheet1.

Hope this helps,

Daniel

Please Login or Register  to view this content.

Excel file to work with office 2007 attached. Sheet1 it's the raw data. Sheet2 should be where results are displayed. Sheet 3 it's a sheet to see how sheet2 should look like after running the macro.
test_file(2007).xls

I have 2 thing that i want to add over the 3 from my initial request, for the ones who can help:

Add 4 more partial strings to search for on point 3, second bullet: "if it's TAR_LCD"a number" it should be pasted on the new line, column G." I will need also for macro to search AD_LCD"a number",PR-LCD"a number",BB_LCD"a number",DSI_LCD"a number".
If possible, can the macro post the new lines + initial lines(columns A to E) on 2nd sheet (sheet2)?

@Daniel,
Thanks for the macro but on my side seams to work only for 1st searched(TAR-WKS)item from columns H,I,J,K. The rest(where are more than 1 on line) of TAR-WKS and TAR_LCD isn't working.
I've attached a file with my test data and your macro - 2test_file.xlsm

Valentin.