excel - How can I amend this to paste special values and use a path specified in a cell range

Question

Currently this code works for me, but I am having problems amending it to do what I want.

There are 2 amends I am after

1. is to use a file path specified in a cell instead of in the script
2. when copying and pasting between workbooks to copy and paste special. I have added a line to value to code before I paste it as when I try and paste special it doesn't seem to look through all my workbooks.

Just some background to the code:

I a folder with multiple sheets and I would like to take the data in the Noms tab from A8:BW (bottom row) and paste it in the workbook I am running the vba from into a sheet called Noms. Each sheet would need to paste under the other.

Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long
Const strPath As String = "C:UsersAmzyDesktopNoms"
ChDir strPath
strExtension = Dir("*.xls*")
Do While strExtension <> ""
    
    Set wkbSource = Workbooks.Open(strPath & strExtension)
    With wkbSource
        LastRow = .Sheets("Noms").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Sheets("Noms").Range("A8:BW" & LastRow).Select:
         Selection.copy
         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        Selection.copy wkbDest.Sheets("Noms").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
        .Application.CutCopyMode = False
        .Close SaveChanges:=False
    End With
    strExtension = Dir
Loop

Answer 1

等待大神答复