How do I call up a line of code that references a cell/range in theactive workbook workbook where I am running my macro from?
E.g. The macro is in workbook1.xls
I wish to copy cell B2 So Activeworkbook.Select Dim Workbook1 Set Workbook1 = Workbooks.Add (or is it Open?)("C:Workbook1.xls") Workbook1.Activate Range("B2").Copy ? |
How do I call up a line of code that references a cell/range in theactive workbook workbook where I am running my macro from?
You don't need to select/activate the workbooks. Is the code in the activeworkbook & you want to copy B2 from workbook1.xls? Code: -------------------- Option Explicit Function IsOpen(wbName As String) As Boolean Dim Wb As Workbook On Error Resume Next Set Wb = Workbooks(wbName) If Err = 0 Then IsOpen = True End Function Sub copyRange() Dim sFilName As String sFilName = "WorkBook1.xls" If Not IsOpen(sFilName) Then Workbooks.Open _ ("C:" & sFilName & ".xls") Workbooks(sFilName).Sheet1.Cells(2, 2).Copy _ ThisWorkbook.Sheet1.Cells(2, 2) End Sub -------------------- -- royUK Hope that helps, RoyUK For tips & examples visit my 'web site ' (http://www.excel-it.com) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=28001 |
How do I call up a line of code that references a cell/range in the active workbook workbook where I am running my macro from?
The ActiveWorkbook object references the workbook that is presently
active in Excel. It may or may not be the workbook that contains the currently executing code. The ThisWorkbook object references the workbook that contains the executing code, regardless of what workbook happens to be active in Excel. Workbooks.Add (or is it Open?)("C:Workbook1.xls") The Add method is used to create a new workbook. The Open method is used to open an existing workbook. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 11 Nov 2008 06:49:47 -0800 (PST), Lav wrote: E.g. The macro is in workbook1.xls I wish to copy cell B2 So Activeworkbook.Select Dim Workbook1 Set Workbook1 = Workbooks.Add (or is it Open?)("C:Workbook1.xls") Workbook1.Activate Range("B2").Copy ? |
All times are GMT +1. The time now is 07:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com