VBA to find cell value and copy entire row to next available row on another sheet

Hi All - Bear with me. I'm very much a VBA newbie and am trying to teach myself best I can. I've seen quite a few threads discussing something similar to what I'm trying to achieve but being new to VBA, I haven't been able to figure out how to modify the solutions to my specific need. Here's my scenario:

I have a workbook with data in multiple sheets. I need to consolidate into a single sheet formatted as an import file for our ERP software. The sheets are named "SO-PREPRO-H", "SO-PREPRO-L", "SO-PREPRO-N", "SO-PREPRO-T", "SO-PREPRO-X". The range of data will need to be dynamic for rows and will go out to "BX" for columns. Also note, each sheet has a header row but I do not want to copy the header info. I need a macro to start in order of the sheets listed and copy the content of the entire row (as values) where column "A" contains a specific letter to a master sheet named "SOIMPORT". For example: "SO-PREPRO-H", copy all rows containing "H" in column "A" to "SOIMPORT" then SO-PREPRO-L, copy all rows where column "A" contains "L" to next open row on "SOIMPORT", (same for "SO-PREPRO-N", "SO-PREPRO-T" & "SO-PREPRO-X).

The ultimate goal is to create a single CSV file that will be used as an order import template. If there is any way at the end of this script, to add code to replace all commas in "SOIMPORT" with a space and then save "SOIMPORT" as a .csv named after the value entered in cell reference "='SO Details'!A1", the would be too good to be true!!!

Thanks so much for any guidance you can provide. I love this site and learn so much from all of you. Hopefully I can get to a point I'll be able to contribute & give back!!!

Best regards,
Brett

Update: At the recommendation of one or our members, I attached a sample file. Note in the live workbook (too large to upload, the actual sheets have formulas to return a value or blank "". Thus the reason I want to paste the entire row as value. Please let me know if anyone has questions and thanks again!!!

here you go:

Please Login or Register  to view this content.

Originally Posted by BBen

Thanks Bishonen. This is exactly what I need. It's so close. When I run this, it copies the first sheet "SO-PREPRO-H" to "SOIMPORT" and then hits a run time error 1004 when it hits Cell "A2" on "SO-PREPRO-L". I tried bypassing that sheet by pulling it out of the script but the same error occurs on the other sheets in the array. I attached a screen shot of the error. Any thoughts?

Thanks again!

please see the attached picture which shows the outcome of one run-through of the code. I opened your file, added a module (dont put the code in any of the worksheets) and pasted the code from the previous post. Which office are you using? Is there any change in the file which you attached and which youre currently using?

Your pdf file has a pic of the vba window. The title of it says that the code is in sheet(7). Can you please delete it from there and put it into a module? thanks
\1