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:

1. Workbook name of destination wb - this I have achieved but using cmd: ThisWorkbook (before prompting the user to do anything)

2. Worksheet name of destination ws - this would preferbly also be read from above code, where it prompt the user with "Set Rng /---/"

3. 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.

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

Solution

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

Comments

[aelveborn]

You already have Rng.Worksheet for the sheet and Rng.Worksheet.Parent for the workbook. You don't need their names.

[Christian]

Tried this, but get Run error nr '91'? It says that objectvariable or With-blocvariabel has not been designated. ?

[aelveborn]

Then you should debug your code to see why your Rng is Nothing.

[Darren Bartrup-Cook]

I'd also add that you don't really need the absolute name in this instance - you'd use that if you need to hardcode to a particular sheet and are worried the user will change the name of the sheet. Here you're using whatever the user gives you and the name isn't likely to change while the code is running - unless the code changes it.

[Vityata]

@DarrenBartrup-Cook - yes, but I did not know the .CodeName property by heart, needed 2 minutes to find it :)

[Darren Bartrup-Cook]

Sorry, was more a comment for the OP that they don't need the codename for this.

[Christian]

This work for the debug window - I get the correct names but not when typing: AppendWs = Rng.Parent.Name AppendWb2 = Rng.Parent.Paranet.Name -> I get same as above " Run error nr '91'? It says that objectvariable or With-blocvariabel has not been designated" (I have Dim then as Worksheet and Workbook respectively earlier in the code)

[Vityata]

@Christian - typing where?

[Admin]

Rng.Parent.Name, etc is returning a string not a worksheet or workbook object (and you need to Set an object). Set AppendWs = Rng.Parentor Set AppendWs = worksheets(Rng.Parent.name)