How to Copying data in VBA from different named workbook each time

How to copy data in VBA from different named workbook each time

Requirement:

The code below does what the user wants but wants to know if it is possible to activate the same range from a different named source workbook each time?

Ideally the user does not want any code in the source file.

The user is trying to avoid putting any code in the source workbook. This is an orderform going out to customers, and the user does not want coded workbooks going to them.

The user would like to start with destination as the active workbook - If the user sets destination as wkb1 is there some way of setting source as wkb2?

The user understands both workbooks must be opened.

Sub RetOrdfrmdata()
'
'
Application.ScreenUpdating = False

' Windows("LOFtestvar.xlsx").Activate - SOURCE WORKBOOK
Range("C74:C118").Select
Selection.Copy

Windows("LJA databasetestvar.xlsm").Activate - DESTINATION WORKBOOK

Range("C54").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("a3").Select

End Sub

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1212541-copying-data-in-vba-from-different-named-workbook-each-time

Solution:

Code:

Sub CopyBetweenWkbs()
' Run this Macro from the Destination Workbook
Dim Mainfile As String
Dim Sourfile As String
Dim fd As FileDialog
Dim fChosen As Integer

Application.ScreenUpdating = False

  Mainfile = ActiveWorkbook.Name          ' Destination
  ' Choose Source Workbook  to be opened ....
  
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  fd.Title = "Please Select File"
  ' Adjust to your situation
  fd.InitialFileName = "D:\My Documents\Excel\*.xls"
  fChosen = fd.Show
            
    If fChosen <> -1 Then
        MsgBox "You Cancelled, Nothing done ... ": Exit Sub
    Else
       Sourfile = Right$(fd.SelectedItems(1), Len(fd.SelectedItems(1)) - InStrRev(fd.SelectedItems(1), "\"))
    End If
    
  ' Open Selected Workbook
  Workbooks.Open (Sourfile)
  
  ' Adjust the Copy Instruction to the specific Sheet Names and Ranges
  Workbooks(Mainfile).Sheets("Sheet1").Range("C74:C118").Value = Workbooks(Sourfile).Sheets("Sheet1").Range("C54:C98").Value
  
Application.ScreenUpdating = True
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Carim.