Copy paste data from another workbook

[DreyFox]

I have the following sheet known as MASTER_CONFIGURATOR.xlsm:

When the "Feed Size BoM's" button is clicked, I would like for it to look for a workbook based on the cell value of A6. For example, the MASTER_CONFIGURATOR file is in a folder known as "Source Files".

The value of A6 right now is BOM1 of F10. When the button Feed Size BoM's is clicked, a new sheet known as BOM1 of F10 is created, then the data from another workbook known as BOM1 of F10 is copied
and pasted into the sheet within the MASTER_CONFIGURATOR known as BOM1 of F10.

The workbook BOM1 of F10 is in the same folder as MASTER_CONFIGURATOR:

I was previously using this set of codes to copy and paste from hard coded excel file names:

Sub Clear_Existing_Data_Before_Paste()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

  Set wsCopy = Workbooks("New Data.xlsx").Worksheets("Export 2")
  Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
   
    '1. Find last used row in the copy range based on data in column A
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
     
    '2. Find first blank row in the destination range based on data in column A
    'Offset property moves down 1 row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
   
    '3. Clear contents of existing data range
    wsDest.Range("A2:D" & lDestLastRow).ClearContents
    '4. Copy & Paste Data
    wsCopy.Range("A2:D" & lCopyLastRow).Copy _
      wsDest.Range("A2")
End Sub

I tried to do the following, but it didn't work:

Sub copypaste_feedsize_bom1()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

Set wsCopy = Workbooks(Range("A6").Value).Worksheets(1)
Set wsDest = Workbooks("MASTER_CONFIGURATOR.xlsm").Worksheets(Range("A6").Value)

lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(2).Row

wsCopy.Range("A1:G" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)

End Sub

Any advice or help would be greatly appreciated. Thank you very much.