title: Get Workbook Name and Worksheet Name from a Range in Excel-VBA
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>How can I however get the Workbook name and Worksheet name from the above selection?</p>
  
  <p>I need this:</p>
  
  <ol>
  <li><strong>Workbook name of destination wb</strong> - this I have achieved but using cmd: <em>ThisWorkbook</em> (before prompting the user to do anything)</li>
  <li><p><strong>Worksheet name of destination ws</strong> - this would preferbly also be read from above code, where it prompt the user with "Set Rng /---/"</p></li>
  <li><p><strong>Workbook name of source wb</strong> - 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 &amp; 4.</p></li>
  <li><p><strong>Worksheet name of source ws</strong> - <em>see 3</em></p></li>
  </ol>
  
  <p><em>Also preferrbly I would like to have the absolute ws name 'Sheet1' etc. not what it is named to (e.g. Kalle Anka).</em></p>
  
  <p>Many thanks!</p>
  
  <p>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?</p>
  
  <p>EDIT2: Based on answers below, I've tried this with following result/problem:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>At these 2 last rows I get Error.</p>
  
  <ul>
  <li>Run error nr '91'? It says that objectvariable or With-blocvariabel
  has not been designated</li>
  </ul>