User enter worksheet name and then use sheet to copy data

[newyears]

I need help on this one. I want user after they select workbook to be prompted to input worksheet name to copy data from that worksheet to another workbook. I'm having issue after user enters worksheet name it errors because name is not what I calling for. I think that I have to set worksheet name but I can't figure out. I'm not concerned with error handling on name of file.

I appreciate any help

    Dim Filter As String, Title As String
    Dim result As String
    Dim FilterIndex As Integer
    Dim wbsource As Workbook
        
    Dim wbdest As Workbook
    Set wbdest = ActiveWorkbook
        Dim Filename As Variant
        FFilter = "Excel Files (*.xls),*.xls," & _
        "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"
' Default Filter to *.*
FilterIndex = 3
Title = "Select a File to Open"
ChDrive ("c")
ChDir ("C:\Documents\")
With Application
    ' ile Name to selected FileSet F
    Filename = .GetOpenFilename(Filter, FilterIndex, Title)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Filename = False Then
    MsgBox "No file was selected."
    Exit Sub
End If
Application.ScreenUpdating = False
    
    result = InputBox("Enter worksheet name")
    wbdest.Unprotect
Set wbsource = Workbooks.Open(Filename)
' Set wbsource.Sheets = result
' Set result = wbsource.Sheets(result)
wbsource.Sheets("result").Range("f8").Copy
wbdest.Sheets("data").Range("b4").PasteSpecial Paste:=xlPasteValues

 

[jmacleary]

Hi there. If you remove the quotes from your second last line to read wbsource.Sheets(result).Range("f8").Copy
that should do it.

 

[mikerickson]

Have you considered this flow chart

GetOpenFilename to select workbook
Open the workbook
Application.InputBox type:=8 to use mouse to select a cell on the source sheet

The type 8 input box removes the problem of misentering the worksheet name.

 

[newyears]

Have you considered this flow chart

GetOpenFilename to select workbook
Open the workbook
Application.InputBox type:=8 to use mouse to select a cell on the source sheet

The type 8 input box removes the problem of misentering the worksheet name.

Removing the quotes worked. I tried this flow and get error on application line -- says argument not optional

 

[mikerickson]

Those are not lines of code to be put in a sub. That was a flow chart to be converted to a VBA routine.