How to refer to a workbook?

[Faraz A. Qureshi]

I usually need to refer to other books during a macro. Lets consider a simple
sample as follows:

Sub SpclMcro()
Dim SrcWS As Worksheet
Dim DstWS As Worksheet
Dim SrcWB As Workbook
Dim Dst As Workbook
Dim Src As Range, Dest As Range
Set Src = Application.InputBox("WHAT?", , , , , , , 8)
Set Dest = Application.InputBox("WHERE?", , , , , , , 8)
MsgBox Src.Workbook.Name
Src.Copy Dest
End Sub

How can I get the name of the workbook of a cell/range as in:

MsgBox Src.Workbook.Name

Furthermore how to select a destination in ANY other workbook because during
the inputbox display of:

Set Dest = Application.InputBox("WHERE?", , , , , , , 8)

the workbook selection mode is inactive?

 

[joel]

You use the property Parent

The parent of range on a worksheet is the sheet.
The parent of a sheet is the workbook

Set Src = Application.InputBox("WHAT?", , , , , , , 8)
SheetName = Src.parent.name
BookName = Src.parent.parent.nam

 

[OssieMac]

Hi Faraz,

Hope you can follow my example. I tried to break it up so it will make more
sense.

Sub SpclMcro()
Dim SrcWS As Worksheet
Dim DstWS As Worksheet
Dim SrcWB As Workbook
Dim Dst As Workbook
Dim Src As Range
Dim Dest As Range

Set Src = Application.InputBox("WHAT?", , , , , , , 8)
'Set Dest = Application.InputBox("WHERE?", , , , , , , 8)

'Set Src = ActiveSheet.Range("A1:A10")

'Worksheet of range Src
Set SrcWS = Src.Parent
MsgBox SrcWS.Name

'Workbook of worksheet SrcWS
Set SrcWB = SrcWS.Parent
MsgBox SrcWB.Name

'Therefore in one line the workbook of range Src
Set SrcWB = Src.Parent.Parent
MsgBox SrcWB.Name

End Sub

 

[OssieMac]

The following commented out line was just used during testing. Please ignore
it. Also no doubt you will work out what you need for Dest.

'Set Src = ActiveSheet.Range("A1:A10")

 

[OssieMac]

Hi yet again Faraz,

Finally worked out an answer to your second question.
"how to select a destination in ANY other workbook because during
the inputbox display the workbook selection mode is inactive?"

You can use the Windows menu to change the workbook selection.
In xl2007 select View Ribbon, Windows block, Switch windows.
In earlier versions select Menu item Window and select required window.