I have adapted this brilliant code, I do however have a question: is there away to get a hyperlink created within the following code. I have attempted it in Range G3 but to no avail.
Sub CreateERFForms()
Dim xlBook As Workbook
Dim xlNewBook As Workbook
Dim xlSheet As Worksheet
Dim LastRow As Long
Dim i As Long
Const strPath As String = "C:\Users\Donald\Documents\Excel Files\Data\" 'Change value to new location
Const strFileB As String = "C:\Users\Donald\Documents\Excel Files\Template.xlsx" 'Change value to new location
Set xlBook = ActiveWorkbook
Set xlSheet = ActiveSheet
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
LastRow = xlSheet.Range("A" & xlSheet.Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Set xlNewBook = Workbooks.Add(Template:=strFileB)
With xlNewBook.Sheets(1)
.Range("A3") = xlSheet.Range("A" & i) 'Change values to destination cell
.Range("B3") = xlSheet.Range("B" & i) 'Change values to destination cell
.Range("C3") = xlSheet.Range("C" & i) 'Change values to destination cell
.Range("D3") = xlSheet.Range("D" & i) 'Change values to destination cell
.Range("E3") = xlSheet.Range("E" & i) 'Change values to destination cell
.Range("F3") = xlSheet.Range("F" & i) 'Change values to destination cell
.Range("G3") = xlSheet.Range("H" & i).Hyperlinks.Add Anchor:=("G3"), Address:=("H" & i), TextToDisplay:=("G" & i)
.Range("C6") = xlSheet.Range("K2") 'Set to single value
.Range("C5") = xlSheet.Range("L2") 'Set to single value
End With
xlNewBook.SaveAs strPath & Format(Now, "yyyymmdd") & "-ID-" & CStr(xlSheet.Range("I" & i)) & "-" & CStr(xlSheet.Range("A" & i)) & "-O.xlsx" 'Naming convention
xlNewBook.Close 0
Set xlNewBook = Nothing
Next i
Set xlBook = Nothing
Set xlSheet = Nothing
Application.ScreenUpdating = True
End Sub
Hoping somebody can help.
Bookmarks