Pasting content from a VBA Workbook to a non-VBA workbook (variable name and variable destination cell/range)

Question

I have 2 workbooks. One is VBA-enabled and the other is non-VBA (I'm calling them wbVBA and wbnonVBA). I have data from wbVBA which I want to paste into wbnonVBA, but it has a variable name so the usual hardcode Dim/Set of Workbook name is not effective.

I managed to create a code which can Dim/Set the wbnonVBA filename based on cell value within wbVBA and search for the variable destination cell/range via .find in wbnonVBA. The code really did work the first time I ran it (except the experimental lines in the middle). But when I closed and opened the 2 workbooks again, I'm now getting a Runtime error '1004'.enter image description here

Here's the code:

Sub PasteToReviewConsolidatedTRIALMERGE()
    
    'WORKING!!! DO NOT TOUCH!!!
    Dim ConsoFileName As Object
    Set ConsoFileName = ThisWorkbook.Sheets("SUMMARY").Range("C1")
    
    Dim DestWB As Workbook
    Set DestWB = Workbooks.Open(Filename:=ConsoFileName)
    
    Reviewer = ThisWorkbook.Sheets("SUMMARY").Range("B2")
    
    ''THESE LINES OF CODE IS STILL UNDEREXPERIMENT!! TO REPLACE ThisWorkbook.Worksheets("User Role").Range("G12:J12").Copy
    'ThisWorkbook.Worksheets("User Role").Range("G12").Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Range(Selection, Selection.End(xlToRight)).Select
    'Selection.Copy
    
    ThisWorkbook.Worksheets("User Role").Range("G12:J12").Copy
    
    DestWB.Sheets("User Role").Select
    Columns("A:A").Select
    Selection.find(What:=Reviewer, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(0, 6).Activate
    ActiveCell.PasteSpecial xlPasteValues

End Sub

Basically, what the code does I believe is the following:

1. Defined the filename of wbnonVBA as the value in sheet(SUMMARY),cell(C1).
2. Used sheet(SUMMARY),cell(B2) as the reference value in finding the cell I want to find.
3. Copy the range in wbVBA.
4. Look for the cell in wbnonVBA, offset(0, 6)
5. Paste.

I think the Runtime error originates from the 1st step, which is defining the filename of wbnonVBA. I kind of just forced this Dim/Set as I'm still new to VBA so no surprises there. Please help me find a better way to do it. I found related questions and solutions here, but I don't think they apply to my problem. Thank you!

Answer 1

Something like this should work:

Sub PasteToReviewConsolidatedTRIALMERGE()
    
    Dim ConsoFileName As String, wbCons As Workbook, wsSumm As Worksheet
    Dim Reviewer As String, f As Range
    
    Set wsSumm = ThisWorkbook.Worksheets("SUMMARY")
    
    ConsoFileName = wsSumm.Range("C1").Value 'should be the #full path#
    Reviewer = wsSumm.Range("B2").Value
    
    Set wbCons = Workbooks.Open(fileName:=ConsoFileName)
    Set f = wbCons.Worksheets("User Role").Range("A:A").Find( _
                    What:=Reviewer, LookIn:=xlFormulas, _
                    LookAt:=xlWhole, MatchCase:=False)
    
    If Not f Is Nothing Then '## check whether the reviewer was found
        ThisWorkbook.Worksheets("User Role").Range("G12:J12").Copy _
            f.Offset(0, 6)
    Else
        MsgBox "No match for '" & Reviewer & "'", vbExclamation
    End If
   
End Sub