User prompt to select a specific workbook and specific sheet within that workbook to cut and paste into current workbook

Question

This code allows me to choose a file and open it, but when I enter the sheet name it gives me

runtime error 91: block variable not set

This code does not close the workbook wb after opening it because I wanted to make sure it was at least opening the workbook, which it is.

What I am trying to achieve:

1. Macro is run on a specific sheet of any workbook (even better if it can be run and automatically navigate to that sheet - the name of the sheet will always be the same, even if the workbook this is run on isn't)

2. Macro prompts user to choose a file and then choose a sheet within that file by name.
   I want to check if the sheet name entered does not exist.

3. Macro cuts specific section of that sheet and pastes it to the sheet in the workbook where the Macro was initially run.

Sub ChooseImportSheet()

Dim w As String, wb As Workbook, myFile As Office.FileDialog

Application.ScreenUpdating = False

Set myFile = Application.FileDialog(msoFileDialogOpen)
With myFile
    .Title = "Choose File"
    .Allow MultiSelect = False
    If .Show <> -1 Then
        Exit Sub
    End If
    FileSelected = .SelectedItems(1)

    w = InputBox("Enter Sheet Name")
    Workbooks.Open(wb)
    wb.Sheets(w).Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks("ThisWorkbook").Activate
    ActiveSheet.Sheets.Range("A4").Select
    Selection.Paste

    Application.Screen Updating = True

End Sub

Answer 1

Try this:

Sub ChooseImportSheet()

    Dim w As String, wb As Workbook, FileSelected
    Dim wsDest As Worksheet, wsSrc As Worksheet
    
    Application.ScreenUpdating = False
    
    With Application.FileDialog(msoFileDialogOpen)
        .Title = "Choose File"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        FileSelected = .SelectedItems(1)
    End With
    Set wsDest = ActiveSheet               'capture the active sheet
    Set wb = Workbooks.Open(FileSelected)  'reference the opened workbook
    
    w = InputBox("Enter Sheet Name")
    On Error Resume Next          'ignore error if no matching sheet
    Set wsSrc = wb.Worksheets(w)  'try to get the worksheet
    On Error GoTo 0               'stop ignoring errors
    
    If wsSrc Is Nothing Then      'sheet not found?
        MsgBox "Sheet '" & w & "' was not found in " & wb.Name
    Else
        With wsSrc.Range("A1")
            Debug.Print "Copying from " & .Address()
            .CurrentRegion.Copy wsDest.Range("A4")
        End With
    End If
   
    'wb.Close false
    Application.ScreenUpdating = True
End Sub