Code optimization and Dynamic changing

Hi,

I have two points I would like to ask your help with:

1- I got help from some people in this forum to write a code that copy and transpose some rows (see the attached file), I have developed the code to fit my needs, however, since I have very poor skills in VBA I wonder if the code can be optimized i.e. written in a more efficient way.

2-In the file (both sheets) there are 22 info fields (columns A to V). this might be changed in the future so that there might be more or less info fields. My question is how to make the code adjusts automatically to the numbers of columns before the "Option" columns? i.e. the code should look for the cell "Option1" regardless of its location and perform accordingly.

Hope I could clarif ywhat I need.

BR
//Emma

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

Thank you John,
the code looks much better, just two points still:
1- in the input sheet the "Options" columns (W,X and Y) are not mandatory, so there might be an entry in a row without any entry in any of the options columns, in this case this row should be copied exactly as is into the output sheet, the code you provided does not copy the rows values from input sheet to output sheet unless they have a value in at least one value in the Options columns.
2-The info headers in row1 in input sheet might have comments which I want them to be taken to the output sheet when the header row is copied there.

BR
//Emma

Maybe:

Please Login or Register  to view this content.

Great!

that works, Thank you John.

B Regards
//Emma

You're welcome. Glad to help out and thanks for the feedback. Please mark this thread as solved.