How do I set a variable in VBA to equal the title of a workbook? And how do I allow it change based on the cell it references?

Question

I am trying to have a cell (A37) equal a variable in VBA and have that variable change which workbook it references based on Cell A37. In A37 I plan to change the name of the workbook to change which workbook it pulls data from.

Cell A37 will have the name of the workbook I want to output from, and Cell A38 will have the name of the worksheet I want to output from (within the workbook from Cell A37).

Here is the code I wrote but I keep receiving errors when I set the OPEXsht equal to a cell. (I'm very new at VBA coding)

Sub CellName ()
Dim OPEXwbk As String
Dim OPEXsht As String

OPEXsht = Workbooks("VBA TRIAL.xlsb").Sheets("Sheet2").Range("A38")

OPEXwbk = Workbooks("VBA TRIAL.xlsb").Sheets("Sheet2").Range("A37")

Workbooks(OPEXwbk).Sheets(OPEXsht).Range("B22").Copy

Workbooks("VBA TRIAL.xlsb").Sheets("Sheet2").Range("A42").PasteSpecial Paste:=xlValues

End Sub

Any help would be greatly appreciated.

Answer 1

Add some error checking at each stage of the process.

Option Explicit

Sub CellName()

    ' This macro in VBA TRIAL.xlsb
    Dim wb As Workbook, ws As Worksheet
    Dim sOPEXwbk As String, sOPEXsht As String
    Dim msg As String, bOK As Boolean
   
    With ThisWorkbook.Sheets("Sheet2")
        sOPEXwbk = .Range("A37") ' book
        sOPEXsht = .Range("A38") ' sheet
    End With
   
    ' check not blank
    If Len(sOPEXwbk) = 0 Or Len(sOPEXsht) = 0 Then
        MsgBox "No workbook or worksheet configured on Sheet2 A37,A38", vbCritical
        Exit Sub
    End If
   
    ' check workbook open
    For Each wb In Workbooks
        msg = msg & vbCrLf & wb.Name
        If wb.Name = sOPEXwbk Then bOK = True
    Next
   
    ' workbook not open
    If bOK = False Then
        MsgBox "'" & sOPEXwbk & "' not in list" & msg, vbCritical, "Workbook not open"
        Exit Sub
    Else
        ' check worksheets
        msg = ""
        bOK = False
        For Each ws In Workbooks(sOPEXwbk).Sheets
            msg = msg & vbCrLf & ws.Name
            If ws.Name = sOPEXsht Then bOK = True
        Next
        
        ' worksheet not found
        If bOK = False Then
            MsgBox "'" & sOPEXsht & "' not in list" & msg, vbCritical, "Sheet Not Found"
            Exit Sub
        Else
            ' OK
            ThisWorkbook.Sheets("Sheet2").Range("A42") = Workbooks(sOPEXwbk).Sheets(sOPEXsht).Range("B22").Value
            MsgBox "Copied B22 from " & sOPEXwbk & " Sheet " & sOPEXsht, vbInformation
        End If
    End If

End Sub