CODEWITHSUNDEEP
excelcopyrangevba
James Watson
James Watson

Reputation: 23

Excel VBA: Get the Workbook associated with a range

This macro needs to start selecting data from various sheets on a user-selected workbook. In other words, I do not know the name of the workbook beforehand. I am asking the user to select a cell in another already open file, so that I can start grabbing data from that book.

Set bookRange = Application.InputBox(prompt:="Select a cell in the sheet from which you wish to import data", Type:=8)
bookName = bookRange.GET_WORKBOOK_NAME_SOMEHOW

Does something like GET_WORKBOOK_NAME_SOMEHOW exist?

Upvotes: 2

Views: 543

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

Once you have a Range it is easy to get the name of the Sheet the range is on and the name of the Workbook the sheet is on.:

Sub dural()
    Dim r As Range
    Set r = Application.InputBox(Prompt:="Pick a range", Type:=8)
    MsgBox r.Address & vbCrLf & r.Parent.Name & vbCrLf & r.Parent.Parent.Name
End Sub

Upvotes: 2

Related Questions