hyperlink error on saving

Hi All,

I have a macro that when run, copies a master sheet, opens a new workbook pastes the copied data, saves & re names the new worksheet & workbook and adds information to a register in the 1st workbook with a hyperlink to the new workbook.

The problem I cant get passed is the hyperlink will only open the folder my new workbook is saved in not the file (obviously I have not got it quite right), I have tried for about 3 days to get it to work looking at other posts here & I can get it to work if I save as a PDF type file but not as an excel type..

I need to be able to bring up the new workbook to add new orders to it from time to time.

Thanks for looking Rex.

HTML Code: 

Draft Sealing Agg Reg 1.xlsm

I would suggest using the HYPERLINK formula is far less tedious. Here's a slimmed down version of your macro, all the unnecessary activating and selecting has been removed.

Please Login or Register  to view this content.

Try this instead:

Please Login or Register  to view this content.

That second method works for me. Odd.

So, we're just wanting a "flat" workbook with no formulas, that's easy enough, let's go another route:

Please Login or Register  to view this content.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

sorrry for the delay in getting back to this - weekend,

ok 1st up I may have miss informed my needs I do require the formulas to stay all except the link between the master sheet & the new sheet number, with that in mind before I try you new code, i have played around with the 1st one changed a few lines & it is working fine

changed code to this

HTML Code: 

Thank you very much for your time much appreciated

cheers Rex

30 seconds of Googling:

FileFormat codes:
51 = xlOpenXMLWorkbook (without macro's in 2007, .xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, .xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro's, .xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007, .xls)