this should work..

[pls123]

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 =
Workbooks"C:\WB\@@@@PLT@@@@ABC.xlsm").Sheets("Sheet1").Cells("D40").Value

 

[Barb Reinhardt]

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

 

[Chip Pearson]

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)

 

[pls123]

ty barb i will work on it in the weekend

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]

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

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]

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