VBA error for formula

[Mike2502]

Hey guys!

I hope you are all well.

I have the following which loops into a folder of excel files and pulls data and pastes onto active worksheet, however I am trying to do a hyperlink formula so I can open the specific excel file for each row of data but the problem I'm getting is that it only pastes the last rows hyperlink for the whole column?

e.g
Column A Column B
Abc Xyz.xls
EFG Xyz.xls
XYZ Xyz.xls

Please see below and many thanks in advance!

Sub GetData()

   '=============================================
   'Process all Excel files in specified folder
   '=============================================
   Dim sFile As String           'file to process
   Dim mylinkpath As String
   Dim wsTarget As Worksheet
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim filename As String
   Dim RowTarget As Long         'output row

   RowTarget = 2
   
   'check the folder exists
     If Not FileFolderExists("C:\Users\me\") Then
      MsgBox "Specified folder does not exist, exiting!"
      Exit Sub
   End If
   
   'reset application settings in event of error
   On Error GoTo errHandler
   Application.ScreenUpdating = False
   
   'set up the target worksheet
   Set wsTarget = Sheets("Sheet1")
   
   'loop through the Excel files in the folder
   mylinkpath = ("C:\Users\me\")
   sFile = Dir("C:\Users\me\" & "*.xls*")
   Do Until sFile = ""
   
      Set wbSource = Workbooks.Open("C:\Users\me\" & sFile)
      Set wsSource = wbSource.Worksheets(1)
      
      'import the data
      With wsTarget
         .Range("A" & RowTarget).Value = wsSource.Range("B4:C4").Value         'ID No
         
         .Range("B2").Formula = "=IF($C2="""","""",Hyperlink(""C:\Users\me\" & sFile & """,""" & sFile & """))"
         .Range("B2:B50").FillDown

 

[Fluff]

Thread closed at OP's request