VBA How to copy data in a closed workbook?

[Adar123]

Hi everyone,

I am trying to copy the data from an active sheet to another file. The destination file contains other data which should not be affected, so I am pointing it the location of paste at the first empty row.

For some reason, I get error "object variable or with block variable not set" when reach the point of this line:

Set wkBk = myApp.Workbooks.Open("DestFile_" & dTod & "." & "xls")

What am I missing?

Extract of VBA code responsible for the action:

dTod = Format(Date, "yyyymmdd")

Set wkBk = ActiveWorkbook
lastRow = Range("A" & Rows.Count).End(xlUp).Row
wkBk.Sheets("NewData").Range("A1:E" & lastRow).Copy

Set wkBk = myApp.Workbooks.Open("DestFile_" & dTod & "." & "xls")
Set wkSht = wkBk.Sheets("NewData")
wkSht.Activate

lastRow = wkBk.Sheets("NewData").Range("A" & Rows.Count).End(xlUp).Row

Range("A" & lastRow).Select
wkSht.Paste

wkBk.SaveAs "DestFile_" & dTod & "." & "xls"
wkBk.Close

 

[Adar123]

Set Rng =wkBk.Sheets("NewData").Range("A1:E" & lastRow)

This solved the issue. Thanks for the code. It does the job.