VBA loop through all open workbooks and stop at first

Hi
I have a macro that is finding a specific product code and deleting the entire row, I then need the macro to loop through all open workbooks and do the same thing if the product code exists but I am struggling to get it to loop through?

It does the first find and delete but then does not carry on through the workbooks.

If possible I also want it to keep looping until it gets back to the first workbook where the macro was started?

Here is what I have so far:

Please Login or Register  to view this content.

Hi nilem,
thank you this is now working and deleting all the rows in my open worksheets. The only issue is now even if the product code does appear on all of my open spreadsheets I get the box come up saying "that name could not be found".
Could this be to do with the personal.xls?

There is another thing that would be amazing if it could be done! Is there a way to make it do this: once the product code has been found and selected, If the cell above the product is blank delete this row too?

Thank you for your help

Originally Posted by JM0308

... the product code does appear on all of my open spreadsheets...

Product code can be located on several (on any)sheets of the book?

I only need the macro to look for the product code on the active worksheet of all the open workbooks, the workbooks do not have more than 1 worksheet. The product code could also not appear in the workbooks and of course at this point it would be ok to display the message but when I am testing I know the product code is on every sheet.

I will open around 50 files, the product code I search for could be on all 50 it could be on 40 of them, I need it to keep looping through all the workbooks even if it doesn't find the code on one of them then stop when it gets back to the first workbook where I called the macro on.

Hope this makes sense

try it

Please Login or Register  to view this content.

Hi Nilem,

amazing!! thank you. this has thrown something new into my logic that I had not thought of though.

Where now it is doing: if the cell above is blank then delete that too.
Can you make it so it does: if the cell to the right is blank then delete the row above the selected cell (product Code)?

sorry to change at the last minute but I didn't see this being an issue until testing

change this line

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

Genius!

I amended the code slightly as I needed it to be AND instead of OR but you were not to know this! It works brilliantly now

I have just had a thought, I do not want the message box to appear every time it fails, in this macro is there a way that we can count how many times it has successfully completed the delete and how many times it has not managed to find the product code and display this in a message at the end of the macro?

"Product Codes removed:
X Successful
Y Unsuccessful"

Please Login or Register  to view this content.

Hi Nilem,

You are brilliant thank you. It works perfectly. Thank you very much

You are welcome