VBA - changing where files are drawn from

[Dave Coram]

Have found the following code on the 'Net and tweaked a little (with some help) to retrieve data from a number of workbooks in a folder, and then present it onto a new spreadsheet.

Public Sub BattingRatings()
On Error GoTo exitloop
Path = "c:\Users\Dave\Desktop\IPL\"
NextFile = Dir(Path & "*.xls")

' open book
Workbooks.Open Filename:=Path & NextFile
' Copy/Paste Data from book
Call RetrieveData
' Close book
ActiveWorkbook.Close savechanges:=False

Do While NextFile <> "" ' Start the loop.
NextFile = Dir
If NextFile = "" Then Exit Sub

' open book
Workbooks.Open Filename:=Path & NextFile
' Copy/Paste Data from book
Call RetrieveData
' Close book
ActiveWorkbook.Close savechanges:=False

Loop
exitloop:
End Sub
Private Sub RetrieveData()
SourceSh = "Ratings"
TargetSh = "Sheet1"

With ThisWorkbook.Sheets(TargetSh)
NxtEmptyRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
ActiveWorkbook.Sheets(SourceSh).Range("A6:A16,AG6:AG16,AH6:AH16,N6:N16").Copy
.Cells(NxtEmptyRw, 1).PasteSpecial xlPasteValues
End With

With ThisWorkbook.Sheets(TargetSh)
NxtEmptyRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
ActiveWorkbook.Sheets(SourceSh).Range("A23:A33,AG23:AG33,AH23:AH33,N23:N33").Copy
.Cells(NxtEmptyRw, 1).PasteSpecial xlPasteValues
End With
End Sub

Please could someone point me in the right direction as to how I would tweak it to allow for the selection of workbooks to be retrieved from, rather than retrieving from all workbooks - would like to update it as we go through the tournament, rather than having to wait to the end!

Have found stuff about GetOpenFilename, and understand the logic, but can't work out how to link that to what I have.

Thanks.

 

[Dave Coram]

Have closed this question and re-entered it, using house rules.