copy values from a worksheet to another workbook. source workbook name unknown

I need to copy a range of values (actually two ranges). The ranges are of fixed size. Le't say A2:D20 and E2:H20.

Both workbooks have the exact same layout, and they both have the same named worksheet (in this case it's called Entry). However the actual filename of the source workbook is unknown. I know numerous users have changed the filename of the xls file.

I want to instruct them to open the old and new workbook, and open a third workbook containing the copy macro, they run the macro and it copies the data.

How can I reference a known worksheet name but of an unknown workbook name esp when the source and destination share the worksheet name?

Hello sark666,

Here is an example macro. This will let the user choose where the Excel source file is stored and open it. The macro will also ask the user to open the destination workbook. The information will be copied from the source range to the destination range. Change the sheet name and ranges to what you are using. They are marked in red.

Sub CopyData()

  Dim DstRng As Range
  Dim DstWkb As Workbook
  Dim DstWks As Worksheet
  Dim FileFilter As String
  Dim Filename As String
  Dim SrcRng As Range
  Dim SrcWkb As Workbook
  Dim SrcWks As Worksheet
  Dim SheetName As String
  
    SheetName = "Output Table"
    
    FileFilter = "Excel Workbooks (*.xls), *.xls"
    Filename = Application.GetOpenFilename(FileFilter, , "Open Source Workbook")
    
    If Filename = "False" Then
       MsgBox "Open Source File Canceled."
       Exit Sub
    End If
    
      Set SrcWkb = Workbooks.Open(Filename)
      Set SrcWks = SrcWkb.Worksheets(SheetName)
      Set SrcRng = SrcWks.Range("A2:H20")
      
    FileFilter = "Excel Workbooks (*.xls), *.xls"
    Filename = Application.GetOpenFilename(FileFilter, , "Open Destination Workbook")
    
    If Filename = "False" Then
       MsgBox "Open Destination File Canceled."
       Exit Sub
    End If
    
      Set DstWkb = Workbooks.Open(Filename)
      Set DstWks = DstWkb.Worksheets(SheetName)
      Set DstRng = DstWks.Range("A2:H20")
      
      SrcRng.Copy Destination:=DstRng
      
End Sub

Hello sark666,

How to mark a thread Solved
Go to the first post
Click Edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No Prefix.
Change to Solved
Click Save