VBA to browse/open workbook then select specific worksheet

[PivotIdiot]

Hi Everyone,

I have a macro that currently runs within a workbook, looks for a named sheet and runs copy/paste functions on certain ranges within.
I'd like to prefix this macro with code that allows me to open another workbook and select a specific worksheet to run the copy/paste functions from there.

Ive started with this....


strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xlsx*),(*.xlsm*),")


If strFileToOpen = "False" Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
Else
Workbooks.Open filename:=strFileToOpen
End If

Dim ws As Worksheet
Dim x As Integer

x = 1

Sheets.Add.Name = "TempSheet"
Sheets("TempSheet").Range("A:A").Clear

For Each ws In Worksheets
Sheets("TempSheet").Cells(x, 1) = ws.Name
x = x + 1
Next ws

I thought i could then work out how to select the required sheet name, store that as a string to use in the copy/paste code further on.

Is there a better way of getting this to be stored for further use?

Thanks in advance, PIV