Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to refer to a workbook?
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? -- Thanx in advance, Best Regards, Faraz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to refer to a workbook?
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.name -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166368 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to refer to a workbook?
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 -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to refer to a workbook?
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") -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to refer to a workbook?
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. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to refer to a cell from another workbook in VBA | Excel Programming | |||
How to Refer to Another Excel Workbook | Excel Programming | |||
Refer to workbook | Excel Programming | |||
Refer to more than one worksheet in a workbook | Excel Worksheet Functions | |||
how to I refer to current workbook without using its name? | Excel Worksheet Functions |