ok heres what ive done: Almost completed
pleas look at the code that has "Problem Here" written in it.

it work fine once I explicitly state the name of the file that is located but I don't want to do that: I need to place the variable closedWorkbook in this spot 
ClosedBookData = "='C:\Users\adrian\Desktop\[ProblemHere]sheet1'!$AU$57:$AU$102"
how can I fix this? ( I don't want to point to a specific file ...because I wont know what the file(workbook) is until it has been determined by the loop below.
thanks.

Public closedWorkbook As Variant
Private Sub CommandButton1_Click()
Dim currentWorkbook As String
Dim tempCurrentWorkbook As String
Dim numInt As Integer
Dim numString As String
Dim newNumString As String
Dim loopTestVar As String
Dim loopPosition As Integer

loopPosition = 6
Application.ScreenUpdating = False

tempCurrentWorkbook = GetBook()
currentWorkbook = tempCurrentWorkbook


Do While loopTestVar <> "("
numString = numString & loopTestVar
loopTestVar = Mid(tempCurrentWorkbook, loopPosition, 1)
loopPosition = loopPosition + 1
Loop

numInt = CInt(numString)
numInt = (numInt - 1)

newNumString = CStr(numInt)
closedWorkbook = Replace(tempCurrentWorkbook, numString, newNumString)
closedWorkbook = closedWorkbook
'MsgBox (closedWorkbook)

Call GetDataFromClosedBook
Application.ScreenUpdating = True

End Sub
Function GetBook() As String
GetBook = ActiveWorkbook.Name
End Function
Sub GetDataFromClosedBook()
 Dim ClosedBookData As String
 
 'first range
 ClosedBookData = "='C:\Users\adrian\Desktop\[ProblemHere]sheet1'!$AU$6:$AU$54"
 With ThisWorkbook.Worksheets(2).Range("A6:A54")
 .Formula = ClosedBookData
 .Value = .Value
 End With
 
 'second range
  ClosedBookData = "='C:\Users\adrian\Desktop\[ProblemHere]sheet1'!$AU$57:$AU$102"
 With ThisWorkbook.Worksheets(2).Range("A57:A102")
 .Formula = ClosedBookData
 .Value = .Value
 End With
 
 'third range
  ClosedBookData = "='C:\Users\adrian\Desktop\[ProbleHere]sheet1'!$AU$105:$AU$145"
 With ThisWorkbook.Worksheets(2).Range("A105:A145")
 .Formula = ClosedBookData
 .Value = .Value
 End With
 
 End Sub