VBA - Import Workbook/Choose Sheet/Display Details

[default_name]

Hello,

This was a follow-on question related to another thread here on the board.
At the recommendation of the board (the original question is more than 1400 days old), as well as the original solution giver, I am posting this with some of my own directed details.

I am trying to do a mixture of a few different things I have found on this board, but I have had difficulty implementing it.
Source posts:
VBA Code to Select Import Worksheet from a Workbook
Macro to Open File Select a Sheet and Copy This to Another File

I have two worksheets in my workbook ('Status' and 'Import').
On sheet 'Status' I have a button that is attached to a macro called 'ImportData'.

1. When the button is pressed, I want the user to be prompted to select an excel workbook.
2. Since the possible workbooks to choose from are all unique in names and content, once a file is selected, I want the user to then be able to identify which worksheet within the desired workbook they would like to import.
   (I found a macro that seemed to accomplish this task quite nicely, but it only works if the desired file is already open.)
3. The desired worksheet is then copied from the chosen workbook, onto my 'Import' tab/worksheet
4. The location of the file is displayed/pasted on cell B6 of the 'Status' worksheet/tab

Here is what I currently have. I feel like I am so close, but there are some snags that I have been unable to overcome on my own.
I added some notes/comments, to hopefully help.

Sub ImportData()
    Dim OpenBook As Workbook
    Dim TargetFile As String

    'Need code here that prompts user to select and open a workbook (no screen updating)
    'The open/selected workbook is recorded as being "TargetFile"
  
    'The following code looks at the newly selected/opened workbook, then prompts the user to choose which worksheet to copy from
    'The information is then copied into the 'Import' tab
    Dim msg As String
    msg = "Which worksheet would you like to copy from this file?"
  
    With Workbooks(TargetFile)
        For i = 1 To .Worksheets.Count
            msg = msg & "(" & i & ") " & .Worksheets(i).Name & vbCrLf
        Next i
  
        response = InputBox(msg, "Type numbers for sheets to import")
  
        If response = Null Then Exit Sub 'check for cancel button
  
        For x = .Worksheets.Count To 1 Step -1
            If InStr(response, x) > 0 Then
                Dim ws As Worksheet
                Set ws = .Worksheets(x)
                ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) 'Copies the chosen worksheet into 'Import' tab
                ActiveSheet.Name = "Import"
            End If
        Next x
    End With
        Sheets("Status").Select    'This routine pastes the path of the chosen/selected file on cell B6 of the 'Status' tab/sheet
        Range("B" & counter + 6).Value = TargetFile
  
    If FileName <> False Then
    End If
  
    'Need code here that closes the chosen workbook file without making any saves or changes (no screen updating)
    Workbooks(TargetFile).Close SaveChanges:=False
    Application.ScreenUpdating = False
  
End Sub

I hope this was clear enough. If not, let me know.
Thanks in advance for your help!

 

[Trebor76]

It worked! Thank you so much! You're a life saver.
I really appreciate it, Robert. Have an awesome day!

Thanks for letting us know and you're welcome. Hope you have a great too