EXCEL VBA - Error when copying from another instance of Excel to active workbook -

what like:

i need able copy columns instance (application) of excel open, depending on header.

what have far:

sub import_data()  dim wb workbook dim c range dim headrng range dim lasthead range dim headrng1 range dim lasthead1 range dim logdate range dim lastrow range dim bottomcell range dim monthrng range dim lastrng range dim prihead range dim loghead range dim typehead range dim callhead range dim deshead range dim ipkhead range dim copyrng range dim monthhead range dim yearhead range  activeworkbook     application.screenupdating = false end  'on error goto errorhandle set wb = getobject("book1")  'if book1 found if not wb nothing     'copy cells      wb.worksheets("sheet1")         set lasthead1 = .range("1:1").find(what:="*", lookat:=xlpart, matchcase:=false, searchorder:=xlbycolumns, searchdirection:=xlprevious)         set headrng1 = .range("a1", lasthead1)         each c in headrng1             if left(c, 1) = "-" c = mid(c, 2, len(c) - 1)             if left(c, 1) = "+" c = mid(c, 2, len(c) - 1)         next c          'insert new column , format month value of log date         set lastrow = .range("a:a").find(what:="*", lookat:=xlpart, matchcase:=false, searchorder:=xlbyrows, searchdirection:=xlprevious)         set logdate = headrng1.find(what:="log date", lookat:=xlpart, matchcase:=false, searchorder:=xlbycolumns, searchdirection:=xlnext)         set bottomcell = .cells(lastrow.row, logdate.offset(0, 1).column)         logdate.entirecolumn.offset(0, 1).insert         logdate.entirecolumn.offset(0, 1).insert         set monthrng = .range(logdate.offset(0, 1), bottomcell.offset(0, -2))         monthrng = "=month(rc[-1])"         monthrng.offset(0, 1) = "=year(rc[-2])"         logdate.offset(0, 1).value = "month number"         logdate.offset(0, 2).value = "year number"         monthrng.entirecolumn.numberformat = "general"         monthrng.offset(0, 1).entirecolumn.numberformat = "general"           set prihead = headrng1.find(what:="priority", lookat:=xlpart, matchcase:=false, searchorder:=xlbycolumns, searchdirection:=xlnext)         set loghead = headrng1.find(what:="log date", lookat:=xlpart, matchcase:=false, searchorder:=xlbycolumns, searchdirection:=xlnext)         set typehead = headrng1.find(what:="type", lookat:=xlpart, matchcase:=false, searchorder:=xlbycolumns, searchdirection:=xlnext)         set callhead = headrng1.find(what:="call status", lookat:=xlpart, matchcase:=false, searchorder:=xlbycolumns, searchdirection:=xlnext)         set deshead = headrng1.find(what:="description", lookat:=xlpart, matchcase:=false, searchorder:=xlbycolumns, searchdirection:=xlnext)         set ipkhead = headrng1.find(what:="ipk status", lookat:=xlpart, matchcase:=false, searchorder:=xlbycolumns, searchdirection:=xlnext)         set monthhead = headrng1.find(what:="month number", lookat:=xlpart, matchcase:=false, searchorder:=xlbycolumns, searchdirection:=xlnext)         set yearhead = headrng1.find(what:="year number", lookat:=xlpart, matchcase:=false, searchorder:=xlbycolumns, searchdirection:=xlnext)         prihead.entirecolumn.copy     end  activeworkbook.worksheets("raw data").cells.clear     activeworkbook.worksheets("raw data")         'paste values         .range("a1").pastespecial xlpastevalues     end      wb.worksheets("sheet1")         wb.application.cutcopymode = false         loghead.entirecolumn.copy     end      activeworkbook.worksheets("raw data")         'paste values         .range("b1").pastespecial xlpastevalues     end      wb.worksheets("sheet1")         wb.application.cutcopymode = false         monthhead.entirecolumn.copy     end      activeworkbook.worksheets("raw data")         'paste values         .range("c1").pastespecial xlpastevalues     end      wb.worksheets("sheet1")         wb.application.cutcopymode = false         yearhead.entirecolumn.copy     end      activeworkbook.worksheets("raw data")         'paste values         .range("d1").pastespecial xlpastevalues     end      wb.worksheets("sheet1")         wb.application.cutcopymode = false         typehead.entirecolumn.copy     end      activeworkbook.worksheets("raw data")         'paste values         .range("e1").pastespecial xlpastevalues     end      wb.worksheets("sheet1")         wb.application.cutcopymode = false         callhead.entirecolumn.copy     end      activeworkbook.worksheets("raw data")         'paste values         .range("f1").pastespecial xlpastevalues     end      wb.worksheets("sheet1")         wb.application.cutcopymode = false         deshead.entirecolumn.copy     end      activeworkbook.worksheets("raw data")         'paste values         .range("g1").pastespecial xlpastevalues     end      wb.worksheets("sheet1")         wb.application.cutcopymode = false         ipkhead.entirecolumn.copy     end      activeworkbook.worksheets("raw data")         'paste values         .range("h1").pastespecial xlpastevalues          'set cells height 15         .cells.rowheight = 15         'set columsn autofit         .cells.columns.autofit     end      'clear clipboard     wb.application.cutcopymode = false     'close book1     wb.close false  else     'if no book1 found display output     msgbox "please ensure have opened data infra" end if  activeworkbook.worksheets("raw data")     'set headers range     set lasthead = .range("1:1").find(what:="*", lookat:=xlpart, matchcase:=false, searchorder:=xlbycolumns, searchdirection:=xlprevious)     set headrng = .range("a1", lasthead)      'remove - or + headers     each c in headrng         if left(c, 1) = "-" c = mid(c, 2, len(c) - 1)         if left(c, 1) = "+" c = mid(c, 2, len(c) - 1)     next c     end errorhandle:  activeworkbook     application.screenupdating = true end  msgbox "new data has been imported"  end sub 

what doesn't work:

the issue appears past function.

the error code:

pastespecial method of range class failed

when debugging highlights of following example of code:

.range("f1").pastespecial xlpastevalues

my findings:

at moment having issues pinning down exact point of failure. seems random paste fails. function completes without issue @ all. thing can think off appears work each time have worksheet pasting on active before run macro. reason thinking because when select debug it, worksheet makes "raw data" sheet active , when press either f8 or f5 debug or run code. works without making other changes.

other notes:

• the workbook copying data exported application , wanting automate process. therefore, workbook has not been selected before macro run. not sure if have bearing on issue?

try like,

.range("g1").pastespecial(xlpastetype.xlpastevalues)