Try this

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
  "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
    szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
  
Sub DownloadFilesfromWeb()
    Dim URL As String, ext As String
    Dim buf, ret As Long, N As Long
    Const strSavePath = "C:\test\" '******  Edit this to reflect your chosen storage location for the local copies.
    
    For N = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        URL = Cells(N, 5).Hyperlinks(1).Address
        Filename = WorksheetFunction.Substitute(Cells(N, 1), "/", " ") & ".pdf" '*** to remove illegal characters from file name
        ret = URLDownloadToFile(0, URL, strSavePath & Filename, 0, 0)
    Next N
End Sub
Credit for the inspiration goes to http://www.mrexcel.com/forum/excel-q...te.htmlhttp://