Input box to select worksheet and column

I have a macro on one workbook, I want to make a macro that pops up asking me to select the workbook and the range for it to do something else.

The workbook name and range i need will be different on each workbook that i use it on hence why i need it to have the input box for selecting.

Sub Getsheet()

Dim desiredSheetName As String
desiredSheetName = Application.InputBox("Select any cell inside the target sheet: ", "Prompt for selecting target sheet name", Type:=8).Worksheet.Name
Debug.Print desiredSheetName

MsgBox desiredSheetName

Workbooks(desiredSheetName).Activate

Range("A1") = "TEST"

End Sub

This only gets the sheet name and not the workbook name so fails at the activate line. I need to get The workbook, the name and a selected range. Im sure this is quite simple but im getting a little stuck.

Thanks for any assistance.

UPDATED

I mustn't have made this clear enough because I thought my request would be rather simple.

I have one workbook which will house macros that I perform on various CVS files.

I open my masterwork book and the file i am working on i run from a button on the master workbook, a macro.

The macro needs to come up with an input box so it makes it clear, straight forward and simple allowing me to select a range on another workbook.

I then need to run the rest of my code using the selected range from the input box.

All I need is how to obtain the workbook name the sheet name and the range from the selection.

hope this clears up any confusion.

Solution

This is what I use (similar situation here):

Sub test2()
    Dim columnStart As Range
    Dim columnNr As Long
    Dim wbC As Workbook
    Dim wsC As Worksheet
    Dim columnAddress As String
    
    On Error Resume Next
        Set columnStart = Application.InputBox("Select the column with the first key", Default:=Selection.Address(0, 0), Type:=8)
    On Error GoTo 0
    If columnStart Is Nothing Then
        MsgBox "Cancelled."
        Exit Sub
    End If
    Set wbC = columnStart.Parent.Parent
    Set wsC = columnStart.Parent
    columnAddress = columnStart.Address
    columnNr = columnStart.Column
    Debug.Print "Workbook: " & wbC.Name & " with worksheet: " & wsC.Name & " & column Address: " & columnAddress & " with column number : " & columnNr
End Sub

The range you get has a parent = Worksheet, which also has a parent of its own = Workbook.
My intent of the code was to not make it my active workbook but just get the key-column so I could base the rest of the code on it.

EDIT: To put it more into context for you:

Sub Getsheet()

    Dim desiredSheetName As String
    Dim rng As Range
    Set rng = Application.InputBox("Select any cell inside the target sheet: ", "Prompt for selecting target sheet name", Type:=8)
    desiredSheetName = rng.Parent.Name
    Debug.Print desiredSheetName
    
    MsgBox desiredSheetName
    
    rng.Parent.Parent.Activate 'Workbook
    Range("A1") = "TEST"
    'or without needing to use Activate:
    Dim wb As Workbook, ws As Worksheet
    Set wb = rng.Parent.Parent 'isn't really needed here
    Set ws = rng.Parent
    ws.Range("A1").Value = "Test"
End Sub