vba to loop thru a table and update another worksheet

Hi folks, I have provided an example workbook of the help I am asking for. I have a form (sheet8) that I input information, then I have code in module 1 that runs when called and copies data off of sheet8 onto sheet1. What I am looking for is there a better way than all of my "IF" statements? I only did for the first four lines of the form on sheet8, but there is 15 lines and three more worksheets with the same form. I numbered them in order, but that may not and usually is never the case. So I was wondering if someone with more knowledge than I could help out with this? Much appreciated and thanks!

Hi thecdnmole,

Try this:

Please Login or Register  to view this content.

Regards,

Robert

It would make more sense if the sheet1 columns were in exactly the same order as your sheet8 data.

This boils down to filtering the data to show all the 'Pass' records. Then assuming the columns are in the same order using code like. Unless there's a particular reason to use looping code then always use filtering for this sort of stuff. Looping code carroes a big time overhead. Particularly where many rows are involved.

Please Login or Register  to view this content.

However you have committed the cardinal sin and have used merged cells on row 13 for the column header labels.
Most of us avoid merged cells like the plague. They just cause too much trouble and will catch you out in all sorts of ways. This is one such example and means you can't use the extremely efficient and simple filtering technique to grab some data and put it somewhere else in just a few lines of code. There is absolutely no benefit in having merged cells here. You can always use cells format 'center across selection' to achieve the same look.

However the column labels immediately above your data should always be unmerged

If you adjust your form so that each field is in a single column then reupload it will then be a trivial task to add the code above. If you want the column labesls in a different position on sheet1 then an Advanced Data Filter would be required

As per Richard's request, cells are now unmerged.

That wasn't quite the end of the merged cells matter. The row 13 column labels shpuld have been a contiguous range.
I've removed all the blank columns in the attached. You may need to tidy up the stuff at the top. As I said try and avoid merged cells ANYWHERE.

This Autofilter will copy exactly the same layout.

If you wanted to limit the output to a few column headers then an Advanced Filter would be needed.

but it is copy/pasting the row that failed. It must skip over that row and only copy the rows that pass. Can this be done?

Not really sure what you mean

The original code looked up what row number to paste via a match formula which there is only one value in Sheet1. I didn't change that part of the code.

As Richard's code presumably doesn't loop as it uses a filter, his would be the preferred method in any case.

Robert

Ah, now I see what you mean. So you changed this line of code...

Please Login or Register  to view this content.

...with this:

Please Login or Register  to view this content.

Thanks for marking the thread as solved and for the rep

Robert

Duplicate post (mod can delete)