CODEWITHSUNDEEP
excelvba
Eduard Salazar
Eduard Salazar

Reputation: 3

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

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!

Upvotes: 0

Views: 51

Answers (2)

Tim Williams
Tim Williams

Reputation: 166316

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

Upvotes: 1

Jie Kong
Jie Kong

Reputation: 42

You can probably try to use Application.GetOpenFilename to get filename, here's an example

    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim wbTarget As Workbook
    Dim wsTarget As Worksheet
    Dim filePath As Variant
    
    ' Set the currently active workbook as the target
    Set wbTarget = ActiveWorkbook
    Set wsTarget = wbTarget.ActiveSheet
    
    ' Open a dialog box for the user to select the source workbook
    filePath = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", , "Select Source Excel File")
    
    If filePath = False Then
        ' If the user canceled the file selection, exit the macro
        MsgBox "Operation Cancelled", vbInformation
        Exit Sub
    Else
        ' Open the selected workbook and set it as the source
        Set wbSource = Workbooks.Open(filePath)
    End If
    
    ' Automatically select the only sheet in the source workbook
    Set wsSource = wbSource.Sheets(1)

Upvotes: 0

Related Questions