But it doesn't make the copy !!..
what's the problem??

----------------------------------------------
Dim aWB As Workbook
Dim aWS As Worksheet

Set aWB = ThisWorkbook
Set aWS = aWB.Worksheets("Sheet1")

aWS.Range("A18").Value =
("Shee t1").Cells("D40").Value

Is the other workbook open? If not,

Try adding something like this (untested)

on error resume next
Dim oWB as workbook
Dim oWS as worksheet

Set oWB = Workbooks(WorkbookName) 'this is something like ABC.xlsm
on error goto 0

if owb is nothing then
Set oWB = workbooks.open(FullWorkbookPath) 'C:\WB\ABC.xlsm'
end if

if not owb is nothing then
MsgBox("No workbook was opened")
Exit sub
end if

Set oWS = owb.sheets("Sheet1")
if not ows is nothing then
aws.range("A18").value = OWB.Sheets("Sheet1").Cells("D40").value
end if
end if

owb.close


--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"pls123" wrote:

But it doesn't make the copy !!..
what's the problem??

----------------------------------------------
Dim aWB As Workbook
Dim aWS As Worksheet

Set aWB = ThisWorkbook
Set aWS = aWB.Worksheets("Sheet1")

aWS.Range("A18").Value =
("Shee t1").Cells("D40").Value

ty barb i will work on it in the weekend :)


"Barb Reinhardt" wrote:

Is the other workbook open? If not,

Try adding something like this (untested)

on error resume next
Dim oWB as workbook
Dim oWS as worksheet

Set oWB = Workbooks(WorkbookName) 'this is something like ABC.xlsm
on error goto 0

if owb is nothing then
Set oWB = workbooks.open(FullWorkbookPath) 'C:\WB\ABC.xlsm'
end if

if not owb is nothing then
MsgBox("No workbook was opened")
Exit sub
end if

Set oWS = owb.sheets("Sheet1")
if not ows is nothing then
aws.range("A18").value = OWB.Sheets("Sheet1").Cells("D40").value
end if
end if

owb.close


--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"pls123" wrote:

But it doesn't make the copy !!..
what's the problem??

----------------------------------------------
Dim aWB As Workbook
Dim aWS As Worksheet

Set aWB = ThisWorkbook
Set aWS = aWB.Worksheets("Sheet1")

aWS.Range("A18").Value =
("Shee t1").Cells("D40").Value

but.......the other workbook is open !!



"Barb Reinhardt" wrote:

Is the other workbook open? If not,

Try adding something like this (untested)

on error resume next
Dim oWB as workbook
Dim oWS as worksheet

Set oWB = Workbooks(WorkbookName) 'this is something like ABC.xlsm
on error goto 0

if owb is nothing then
Set oWB = workbooks.open(FullWorkbookPath) 'C:\WB\ABC.xlsm'
end if

if not owb is nothing then
MsgBox("No workbook was opened")
Exit sub
end if

Set oWS = owb.sheets("Sheet1")
if not ows is nothing then
aws.range("A18").value = OWB.Sheets("Sheet1").Cells("D40").value
end if
end if

owb.close


--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"pls123" wrote:

But it doesn't make the copy !!..
what's the problem??

----------------------------------------------
Dim aWB As Workbook
Dim aWS As Worksheet

Set aWB = ThisWorkbook
Set aWS = aWB.Worksheets("Sheet1")

aWS.Range("A18").Value =
("Shee t1").Cells("D40").Value

The source workbook needs to be open. Moreover, when you reference
another (open) workbook, you use only the file name (e.g. "ABC.xls")
not the complete path to the workbook (e.g., "C:\Test\ABC.xls").

aWS.Range("A18").Value = _
Workbooks("ABC.xls").Sheets("Sheet1").Range("D40") .Value

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 13 Dec 2008 04:50:05 -0800, pls123
wrote:

But it doesn't make the copy !!..
what's the problem??

----------------------------------------------
Dim aWB As Workbook
Dim aWS As Worksheet

Set aWB = ThisWorkbook
Set aWS = aWB.Worksheets("Sheet1")

aWS.Range("A18").Value =
("She et1").Cells("D40").Value

now its ok.. !!
i was looking for the "workbooks.sheets.range" trick by longtime..
ty everybody


"Chip Pearson" wrote:

The source workbook needs to be open. Moreover, when you reference
another (open) workbook, you use only the file name (e.g. "ABC.xls")
not the complete path to the workbook (e.g., "C:\Test\ABC.xls").

aWS.Range("A18").Value = _
Workbooks("ABC.xls").Sheets("Sheet1").Range("D40") .Value

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 13 Dec 2008 04:50:05 -0800, pls123
wrote:

But it doesn't make the copy !!..
what's the problem??

----------------------------------------------
Dim aWB As Workbook
Dim aWS As Worksheet

Set aWB = ThisWorkbook
Set aWS = aWB.Worksheets("Sheet1")

aWS.Range("A18").Value =
("She et1").Cells("D40").Value