looping through multiple workbooks copying file paths as Hyperlinks run time error 5

[Davavo]

Hi,
i think this is just a syntax issue but i cant seem to get it right. If anyone can help i would be very grateful.

this script works at looping through all of the workbooks in a user defined folder and copying a specified range to a big list in the target worksheet of the workbook where the macro is.
Each row in the target worksheet contains all of the data read from one source workbook.
What i want to do is copy an additional range, one cell in each source workbook that contains the filepath of the source workbook.
I want to create this hyperlink in the targetsheet, in column A, in the correct row obviously, and display the row number, as the hyperlink.
I am not quite sure what i am doing wrong with this.
I am getting run time error 5, invalid procedure call of argument.
I guess the syntax is wrong.

Any help would be greatly appreciated.

Sub ReadExpenses2()'PURPOSE: To loop through all Excel workbooks in a user specified folder, copy a set range from those files, including a cell that specifies the filepath.


Dim wb As Workbook
Dim TWB As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim Trgtws As Worksheet
Dim ws As Worksheet
Dim SWS As Worksheet
Dim ob As ListObject
Dim SpaceCell As Range


Dim UsedRows As Long
Dim LR As Long




Set TWB = ThisWorkbook
Set Trgtws = TWB.Sheets("Expenses")


'remove protection
Sheets("Expenses").Unprotect password:="Dave"


'Turn off hoggs
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual


Call killfilterEXP 'shows all data if th filer is in use


'Remove Totals
With Trgtws.ListObjects("TExpenses")
               .ShowTotals = False
End With




'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)


    With FldrPicker
      .title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With


'In Case of Cancel
NextCode:
    myPath = myPath
        If myPath = "" Then GoTo ResetSettings


'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"
  
'Target Path with Ending Extention
    myFile = Dir(myPath & myExtension)


    'Loop through each Excel file in folder
    Do While myFile <> ""
    
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(FileName:=myPath & myFile)
      Set SWS = wb.Sheets("ALL")
    
 
'---------------------here i want to copy the filepath from K10 of sheetname 'Sumamry' in the source workbook and paste it into the correct row in column A of the target worksheet of the workbook with the macro
    
Dim r As Long


LR = SWS.Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
UsedRows = Trgtws.Columns("B").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
r = UsedRows + 1




SWS.Range("A2" & ":S" & LR).Copy


Trgtws.Range("B" & UsedRows + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Trgtws.Hyperlinks.Add anchor:=Range("A" & UsedRows + 1), Address:=SWS.Range("K10"), TextToDisplay:=r    '----------but this code is wrong ---- ERROR HERE


    
    '----------------------------------------------------------------------------------------------------------------------------------------------------






    'Save and Close Workbook
    wb.Close SaveChanges:=False
      
    'Ensure Workbook has closed before moving on to next line of code
    DoEvents


    'Get next file name
    myFile = Dir
      
Loop

 

[Davavo]

solved it

Dim i As Long
Dim istring As String

LR = SWS.Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
UsedRows = Trgtws.Columns("B").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row

SWS.Range("A2" & ":S" & LR).Copy

Trgtws.Range("B" & UsedRows + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    For i = 2 To UsedRows
      If Trgtws.Cells(i, 2) <> "" And Trgtws.Cells(i, 1) = "" Then
         istring = i
         Trgtws.Hyperlinks.Add Trgtws.Range("A" & i), Address:="file:///" & myPath & myFile, TextToDisplay:=istring
      End If
    Next i