Code to copy and paste row from one excel sheet to another, based on criteria

Hi CamSeren,

I learned how to program macros on excel by recording a macro with the actions I needed, and modify it according to my needs. Having some basic programming knowledge and understanding helps a lot.

Trying to help you, but also to provide you a way of learning, I did exactly this thing: I recorded a macro performing what you needed, then modified the code, and you can find it below.

The code works only once, and this is what it does:
- it finds the first occurance of the word "Qualified" in column G
- selects and cuts the line the word is in
- switches to Sheet1 and selects first free cell in column A
- pastes the cut line
- switches back to Sheet2 and deletes the (empty) line

Certainly, once you insert the Refresh button that you mentioned, you can add a loop in the code behind it, so that it calls repeatedly the macro. The macro is not finished, e.g. has no provision of what will happen if searched string is not found. But I guess that if you're experienced enough to insert a working button, you can handle it.

Good luck

Please Login or Register  to view this content.

Hi jbg. Thank you for this, and the tip about recording a macro first. It is definitely a new learning curve for me.

I have inputted the code but it keeps coming up with the error message: 'Object variable or with block variable not set'. Apologies if I am confusing the situation!

Thank you for the quick response.
CamSeren

You can debug the subroutine (Press F8 to execute each line of code) to check which is the undefined variable. Check the Sheets names would be my first suggestion. If they are correct (according to the code, Sheet1 and Sheet2), then debug, else, change the sheet names in the code.

Hi jbg with a few tweaks this now works great. Thank you very much for your help

You're welcome