Dear sir,i come forward with the same issue as before with a SCHEMA attached.
Create a workbook G from another workbook E with hyperlink.
You originally posted:
A: D:\TMC.TX MAIN ACTIVITIES 2015
B: D:\TMC.TX MAIN ACTIVITIES 2015\TMC.TX ACTIVITIES 2015
E: FEB2015 TMC.TX ACTIVITIES Since this will contain a macro, it must be named
FEB2015 TMC.TX ACTIVITIES.xlsm
C: D:\TMC.TX MAIN ACTIVITIES 2015\TMC.TX E-FAULT DOCKET 2015
F: TT15013001.xlsx (I assumed the .xlsx)
So I put this code in FEB2015 TMC.TX ACTIVITIES.xlsm by right clicking on the tab named Sheet1. In the resulting code module I put in this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
If Len(Trim(Target.Value)) <> 8 Then Exit Sub
sName = Trim(Target.Value)
sSourcePath = "D:\TMC.TX MAIN ACTIVITIES 2015\TMC.TX E-FAULT DOCKET 2015\"
sSourceName = "TT15013001.xlsx"
sDestPath = "D:\TMC.TX MAIN ACTIVITIES 2015\TMC.TX E-FAULT DOCKET 2015\"
sDestName = sName & ".xlsx"
Workbooks.Open SourcePath & sSourceName
ActiveWorkbook.Worksheets(1).Name = "Sheet1"
ActiveWorkbook.SaveAs sDestPath & sDestName
For Each hlnk In Me.Hyperlinks
Me.Hyperlinks.Add Anchor:=Target, _
Address:=sDestPath & sDestName, _
when I put an 8 character string in cell A1 of Sheet1 of FEB2015 TMC.TX ACTIVITIES.xlsm
the code creates a new workbook in D:\TMC.TX MAIN ACTIVITIES 2015\TMC.TX E-FAULT DOCKET 2015 by opening TT15013001.xlsx and saving it in the same path with a name of the 8 character string in A1 of Sheet1 of FEB2015 TMC.TX ACTIVITIES.xlsm and then create a hyperlink in cell A1 of sheet1 of FEB2015 TMC.TX ACTIVITIES.xlsm that opens the new workbook when clicked. The hyperlink is to Sheet1, column A row 1 (A1) so I have assumed that TT15013001 has a worksheet named Sheet1.
I believe that is what you asked for. (Also, in your first request you said nothing about creating a hyperlink to the workbook).
Given the above description, I have tested this code and it worked fine for me. So if it doesn't work for you, you will have to make any changed required. (such as my assumption about the TT15013001 having .xlsx extension.