Get Workbook Name and Worksheet Name from a Range in Excel-VBA

Question

I have a bit of code now that prompt the user to select a range (1 area, 1 column, several rows). This is the code where it prompt the user to do so:

MsgBox "Select a continuous range of cells where numeric values should be appended."

Set Rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8) 'Type Values, 8 - Range object

How can I however get the Workbook name and Worksheet name from the above selection?

I need this:

Workbook name of destination wb - this I have achieved but using cmd: ThisWorkbook (before prompting the user to do anything)
Worksheet name of destination ws - this would preferbly also be read from above code, where it prompt the user with "Set Rng /---/"
Workbook name of source wb - after reading the destination ws, I want to promt the user with a Open-dialuge to select the source Workbook, where I will promt the user to select an additional range (source range) - which will be input to 3 & 4.
Worksheet name of source ws - see 3

Also preferrbly I would like to have the absolute ws name 'Sheet1' etc. not what it is named to (e.g. Kalle Anka).

Many thanks!

EDIT: I know it in the input-dialouge show if another ws or wb is selected than from where the macro was initiated, i.e. '[Cognos Orders and deliveries.xlsx]Truck Orders'!$F$11:$F$18. But if I dim Set as Range - is there any way to retrive that info? If it were a String you could maybes split the String with ! and then ] to get the ws and wb seperately? How now with a Range?

EDIT2: Based on answers below, I've tried this with following result/problem:

Sub AppendCognosData()

Dim Rng As Range

Dim AppendWb As Workbook
Dim AppendWs As Worksheet
Dim AppendWb2 As Workbook

'Create a reference to Wb where to append data
Set AppendWb = ThisWorkbook

MsgBox "Select a continuous range of cells (in a column) where numeric values should be appended."

Set Rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8) 'Type Values, 8 - Range object

AppendWs = Rng.Parent.Name
AppendWb2 = Rng.Parent.Paranet.Name

At these 2 last rows I get Error.

Run error nr '91'? It says that objectvariable or With-blocvariabel has not been designated

You already have Rng.Worksheet for the sheet and Rng.Worksheet.Parent for the workbook. You don't need their names. — GSerg
Tried this, but get Run error nr '91'? It says that objectvariable or With-blocvariabel has not been designated. ? — Christian
Then you should debug your code to see why your Rng is Nothing. — GSerg

Vityata Vityata · Accepted Answer · 2018-06-25T08:19:32

To refer to a worksheet with a string variable, this is the syntax:

set wks = Worksheets("NameOfWorksheet") Worksheets MSDN

To refer to a workbook with a string variable:

set wkb = Workbooks("NameOfWorkbook") Workbook MSDN

Now, the problem is how to get the strings "NameOfWorksheet" and "NameOfWorkbook" from the selection. This is some minimal example:

Public Sub TestMe()

    Dim wks As Worksheet
    Dim wkb As Workbook
    Dim rng As Range
    Set rng = Selection

    Debug.Print rng.Parent.Name            'Name of the worksheet
    Debug.Print rng.Parent.Parent.Name     'Name of the workbook
    Debug.Print rng.Parent.CodeName        'Code Name of the worksheet

    Set wks = Worksheets(rng.Parent.Name)
    Debug.Print wks.Name

    Set wkb = Workbooks(rng.Parent.Parent.Name)
    Debug.Print wkb.Name

End Sub

Get Workbook Name and Worksheet Name from a Range in Excel-VBA

1 Answers