VBA Pulling Data From Other Sheets Remove Duplicates

Hey All,

I have three worksheets in this sample workbook, but my real workbooks could have many more worksheets. My goal is to create a macro that will pull all of the row data from the "160" and "300" worksheets onto the "Main" worksheet, but I don't want to have duplicates on the "Main". The "160" and "300" worksheets will have some identical rows.

I would like to have the data in the "main" sheet sorted by Column A and then by Column C and D. Also, I would like to leave a couple blank rows after there is a switch to the new symbols in column A.

Column F will have clickable hyperlinks that can vary in length depending on the length of the article title. If the title goes past the page break, I would like to create a new row to continue F column data directly below the corresponding row.

It is important to add that there could be hundreds of rows depending on the day, so the macro would need to look down the columns and not be limited to the sample worksheet data provided.

Any help would be much appreciated. I have attached the spreadsheet.

Hey dflak,

Yes, each of the 6 cells in the row are dependent on each other.

The reason for the hyperlink error is because it coming from a FactSet database. You must have the Factset add-in to be able to view the link, which is a paid subscription (I don't know how familiar you are with the database).

I have a code in cell B2 that basically transposes identifiers(column B) down the columns looking back 24hours...Date(Column C), Time the news article is released (Column D), News Source prefix (Column E) and Column F is the title of the link as a hyperlink, which if clicked by the user(with Factset access) will open up a html file of the article. Column A is only a VLOOKUP looking at Column B.

I am sorry for the poor details on my part. I wish I was able to figure the macro out myself, but I am new to VBA and am not able to 'record' something like this because of the complexity. Now I am wondering if this is even possible with a hyperlink involved?

Yes, the links work for me and they should be kept in place.

The code looks great. Thank you for putting in the amount of time you have.

The insertion of new rows is not as important as the the 'link title' extending past the page break. Ultimately, I am going to export this to pdf and I would like it to look somewhat clean without the link title being cutoff after only a couple words. If it is better to extend column F that will work. I would only like it to look as clean as possible.

Thank you again.

Thank you very much! I am going to try and implement your code in the larger workbook. Thanking for the comments within the code as well it will be very helpful.