Workbook to workbook info transfer with dynamic names on button

[jackers15]

Hi,

So I have a button set up with the following code:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub DynamicNamePull()

   Application.ScreenUpdating = False

   Dim beginPull As Action
   beginPull = pull("Settings!Q15" & "Settings!O2" & "Settings!Q16")
  
   Application.ScreenUpdating = True

End Sub
</code>

The pull function called is the following:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Function pull(xref As String) As Variant

Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, c As Range, n As Long

n = InStrRev(Len(xref), xref, "")

If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then
b = b & Mid(xref, Len(b) + 2, n)
Else
n = InStrRev(Len(xref), xref, "!")
If n > 0 Then
b = Left(xref, n - 1)
End If
If Left(b, 1) = "'" Then
b = Mid(b, 2)
On Error Resume Next
If n > 0 Then
If Dir(b) = "" Then
n = 0
Err.Clear
On Error GoTo 0
End If
If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
pull = Evaluate(xref)
If CStr(pull) = CStr(CVErr(xlErrRed)) Then
On Error GoTo CleanUp
Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add
On Error Resume Next
n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)
Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))
If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, x1R1C1))
Next
pull = r.Value
End If
End If

CleanUp:
If Not xlwb Is Nothing Then
xlwb.Close 0
If Not xlapp Is Nothing Then
xlapp.Quit
Set xlapp = Nothing
End If

End Function</code>
I keep getting a mismatch '13' error. I am trying to have the button run a function to pull a cell in another closed workbook and print it in a cell in the main workbook. I have the formula: =PULL("'H:\Projects\[" & O2 & "]CoverSheet'!J15") in the first cell I need it to print to. The continuing formulas just change the O row (ex: O3, O4, etc). I am running Excel 2010.

Thanks in advance for any help.

 

[jackers15]

Sorry about the missing code tags. And I'm not able to edit it to put them in.