I have an Excel workbook with a dozen (or more) worksheets and each worksheet has up to 200 rows of text data and column B is very relevant in each sheet (it contains server names) . All worksheets have the same structure (row 1 is identical) , only the data is different.
Now, periodically I receive a list of servers which need to be deleted from the workbook and the problem is that I don't know in which sheet and row is the data I'm looking for.
The list of servers to be deleted is appended to the workbook as a new sheet named "to delete" and data is pasted in column A (one server / row).
I found a very interesting script here : [url removed, login to view]
The problem with this script is that is asking for the string to be searched interactively and I need to do a for loop on the column of servers to be deleted. Feel free to use it and work on top of it.
So this is what I want from this script :
- once I run a shortcut assigned to a module, the code will perform this :
1. Creates the additional result sheet (like the link above)
2. Starts looping for each server in the "to delete" sheet and for each cell it searches the whole workbook (except the "to delete" sheet). When the string is found, it paints the specific row in a color and adds a link to that row or cell in the results sheet.
When the script has finished running, I expect to find rows colored in each exiting sheet when a server name was found. Additionally, I want to have the result sheet (like the link above) with a list of all occurrences of the searched strings and links to them
I intend to click on the link, go to cell, right click and delete. If this works, we can work again on automating the deletion process.
Please let me know if you need more information.