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

?

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

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

?