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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 08:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com