create hyperlink of range of cells in a column using worksheet event

Hello All, I have a worksheet event that does not seem to work and I am not sure why:

My spreadsheet creates a number of order lines from Q2 to AY50 (could be any number of lines 5, 10 ... depending on the order), now in column S is the web address for the order location this gets copied on every line of the order (i.e. s2, s3...).

Now I have a macro that copies this range of order lines to my tracker sheet but only copies as text so I edited the below code I found on the internet to change the range once it gets to my tracker sheet but it is not working, any ideas.

Please Login or Register  to view this content.

it is giving me a PasteSpecial method of Range class failed on the xlPasteAll line

Thank you ALphaFrog, that will take a while to create a cleansed workbook, too many moving parts. Is there no way of doing my original ask, when ever new lines are pasted to the spreadsheet, it automatically hyperlinks any text in Column S, seems like the simplest solution.

Update on the question, I was able to figure out the macro which works for a manual solution, stupid rookie mistake (worksheet was protected).

Now hat I have done is used the hyperlink macro to create the below worksheet event which fires after a number of rows (varies) are pasted (values only because other worksheet is using cell references) from another worksheet. It worked perfectly the first 3 times then just stopped, any idea why?

Please Login or Register  to view this content.

Hi can anyone assist, I find it really odd, that the above event would work exactly 3 times and not work again.

Hi DavesExcel/AlphaFrog, sorry, I understand, was just too much going on to create a sample workbook.

Ok, please find attached the HW Orders workbook, note there are only two macros COPY to copy from HW Orders to Tracker (values only, this is because on the original workbook HW Orders rows have several cell references) and ADD_LINKS to create the links manually from the tracker sheet.

Also in the Tracker sheet there is the code for a worksheet event that would do this automatically after the paste happens, this is what I am trying to accomplish or a way of adapting the copy/paste lines that the include the link when the rows are pasted

Thank you Alphafrog, sorry for the late reply, it works, much appreciated.