Open File in Directory with a Part of Filename as String

Hi all,
I have a problem and will try to explain it as good as I can =)

I have a Workbook "forecast.xlsx" and several Workbooks which have a individual Number in the Filename (E.g. "3960........xls", "3961.......xls etc.) in the same directory as "forecast.xlsx" + \projects\"

Now, I would like to browse through "forecast.xlsx" in column H, searching for hyperlinks. In Column H are these Projectnumbers located which are used in the filenames above. Whenever the Cell cointains a hyperlink, following should happen (that far i managed it by myself):

- Store the Cellvalue in a String Variable
- Go to the Directory this.workbook + \projects\
- browse this folder, searching for a File which contains the stored string in its filename
- open the file
- copy a range
- close file
- This.workbook.Activate
- follow the Hyperlink which has been stored as String before
- paste selection
- continue browsing through column H, looking for the next hyperlink.


I have a VBA which runs bugless, but it copies the wrong range, but to the correct destinantion.

see the code below:

Sub RESLT_INPUT_ALL()
Dim cell As Object
For Counter = 1 To 1000
Set cell = Worksheets("Projects overview").Cells(Counter, 8)
If cell.Hyperlinks.Count > 0 Then
Dim FileName As String
Dim app As New Excel.Application
app.Visible = False 'Visible is False by default, so this isn't necessary
Dim Book As Excel.Workbook

FileName = FindFile(cell.Value)

If FileName <> "" Then



Set Book = app.Workbooks.Add(FileName)

Book.Worksheets("overview").Activate

Cells.Select

Selection.Copy

cell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Book.Close SaveChanges:=False
app.Quit
Set app = Nothing
End If
End If
Next Counter


End Sub


Function FindFile(ProjectNumber As String) As String

Dim MyObj As Object, MySource As Object, File As Variant
File = Dir(ThisWorkbook.Path & "\packages\")
While (File <> "")
If InStr(1, File, ProjectNumber) > 0 Then
FindFile = ThisWorkbook.Path & "\packages\" & File
Exit Function
End If
File = Dir
Wend
FindFile = ""
End Function




I Hope you understood my problem and can help me with creative suggestions

The main problem is, to find the File in the folder. i tried it with the InStr Function but i'm not sure what it does and how it works...