I am looking for a VBA Macro to grab information from a table on a website (which will be supplied to our shortlist).
In a cell you will be given a name that needs to be searched on the website. This will then take you to a summary sheet. To get to the table you will need to click on a link on that page, the name of the link does not change.
Occasionally the search will produce more than one result, 95% of the time we will then be looking for the latest date in the search, the other 5% of the time we can manually enter the data. This name would then have to be clicked. The search results are not in date order, but you can click on the column header to sort them in date order. Once you click on the name it will take you to the summary sheet above.
Once you have clicked on the link to take you to the table you need to download a table (which is identifiable using Excel Get External Data) into Excel.
Each time the macro runs it will have to repeat the process up to 6 times. Each name will be in a different cell and each table needs to be pasted onto a separate worksheet which already exists.
If on the summary sheet the page has no link to the results table I would either want a pop up to say ""name" has no data", or it can leave the table it pastes blank.