this should work..

P

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
 
B

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
 
C

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)
 
P

pls123

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


Barb Reinhardt said:
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.
 
P

pls123

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



Barb Reinhardt said:
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.
 
P

pls123

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

fill a cell with this.. 4
launch macro in other workbook 3
calling sub from other workbook 1
error 1004 6
range calculate 5
Switching sheets while timer running 0
the rigt syntaz 2
timer issue 5

Top