Copy and Paste in closed workbook

[rizmomin]

Hello:

I need a VB Code to copy the data from current file, Sheet "Summary" Range B5:E30 to closed workbook at path :C:\Temp\2014-File.xls\ Sheet "Employees at location T2.
Basically i do not want to manually open the destination file and do and paste.

Please let me know if you have any questions.
Thanks.


Riz

[ranman256]

start with an empty wb.
Record a macro (to PERSONAL WORKBOOK)
open the workbook
copy
paste
save
close
end recording.

your macro will work w/o you 'opening' the workbook.

[Leith Ross]

Hello rizmomin,

Code for reading and writing from closed workbooks has restrictions. So, to be sure that the code will work correctly it would help to have a copy. PLease redact any sensitive information from the workbook before you post it.

FYI ranman256 - You can not record the macro code to read or write to a closed workbook. To do so requires using Active Data Objects (ADO).

[rizmomin]

Hi Leith:

Thanks for your great input.
Please find attached 2 files, one Source and other one Destination file(08_2014_DailySales).
Data for Source file will be in "Summary" tab as highlighted.
These data needs to be pasted to closed workbook (name of the file in cell c1 of source file) in Employees Sheet at T2.

I hope this is clear and VB code can do the trick.
Please let me know if you have any questions.
Thanks.


Riz

[Leith Ross]

Hello Riz,

Thanks for posting the workbooks. Makes it easier to write and test the code.

[rizmomin]

Hi Leith and Others:

I am trying to use below code to accomplish my goal but it is not gong any further and it is erroring out at : Value1 = Sheets("Summary").Range("B5:E33")

Please Login or Register  to view this content.

Please help to resolve.
Thanks
Riz