excel - Error when running a macro if open a different workbook is open -

excel - Error when running a macro if open a different workbook is open -

hi wondering if help me, have (below) code in module, however, if in different open workbook error message pops up. guessing trying execute macro in current selected workbook instead of needed workbook ("mkl"). below code.

dim timetorun sub auto_open() phone call schedulecopypriceover end sub sub schedulecopypriceover() timetorun = + timevalue("00:01:00") application.ontime timetorun, "copypriceover" end sub sub copypriceover() application.displayalerts = false dim mypath string dim myfilename string dim celltxt string calculate workbooks("mkl.xlsm").sheets("data quarter hourly").select phone call schedulecopypriceover workbooks("mkl.xlsm").sheets("data quarter hourly").rows("9:9").insert shift:=xldown, copyorigin:=xlformatfromleftorabove workbooks("mkl.xlsm").sheets("data quarter hourly").range("datenow:stock2").copy workbooks("mkl.xlsm").sheets("data quarter hourly").range("a9:c9").pastespecial paste:=xlvalues, operation:=xlnone, skipblanks:= _ false, transpose:=false range("d10:cb10").copy workbooks("mkl.xlsm").sheets("data quarter hourly").range("d9:cb9").pastespecial paste:=xlformulas, operation:=xlnone, skipblanks:= _ false, transpose:=false celltxt = workbooks("mkl.xlsm").sheets("trades").range("c2").text if instr(1, celltxt, "a") or instr(1, celltxt, "b") mypath = "z:\capital\research - internal\arb trading models\trades" myfilename = "trades " & format(now(), "dd-mmm-yyyy hh-mm-ss") if not right(mypath, 1) = "\" mypath = mypath & "\" if not right(myfilename, 4) = ".xls" myfilename = myfilename & ".xls" workbooks("mkl.xlsm").sheets("trades").copy activeworkbook .saveas filename:= _ mypath & myfilename, _ local:=true, _ fileformat:=xlworkbooknormal, _ createbackup:=false .close false end end if application.displayalerts = true end sub sub auto_close() on error resume next application.ontime timetorun, "copypriceover", , false end sub

any help much appreciated.

i noticed few things throw off code due implicit references.

i went through copypriceover , replaced implicit reference more explicit one, useda worksheet , workbbok objects , added few comments:

sub copypriceover() application.displayalerts = false dim mypath string dim myfilename string dim celltxt string dim wb workbook: set wb = workbooks("mkl.xlsm") '<~~ set workbook object wb workbook "mkl.xlsm", save lot of writin , improve readability dim wsdataquarterhourly worksheet: set wsdataquarterhourly = wb.worksheets("data quarter hourly") '<~~ set worksheet object reference "data quarter hourly" sheet in mkl.xlsm workbook, utilize of above wb object dim wstrades worksheet: set wstrades = wb.worksheets("trades") '<~~ set worksheet object reference "trades" sheet in in mkl.xlsm workbook calculate wsdataquarterhourly.select '<~~ don't see need select it? may wrong, if omitted happens execution? phone call schedulecopypriceover wsdataquarterhourly.rows("9:9").insert shift:=xldown, copyorigin:=xlformatfromleftorabove '<~~ using worksheet object wsdataquarterhourly.range("datenow:stock2").copy '<~~ not aware reference ranges that? , not working on end wsdataquarterhourly.range("a9:c9").pastespecial paste:=xlvalues, operation:=xlnone, skipblanks:= _ false, transpose:=false 'next line should updated range("d10:cb10").copy '<~~ want copy? if "data quarter hourly" wsdataquarterhourly.range("d10:cb10") wsdataquarterhourly.range("d9:cb9").pastespecial paste:=xlformulas, operation:=xlnone, skipblanks:= _ false, transpose:=false celltxt = wstrades.range("c2").text if instr(1, celltxt, "a") or instr(1, celltxt, "b") mypath = "z:\capital\research - internal\arb trading models\trades" myfilename = "trades " & format(now(), "dd-mmm-yyyy hh-mm-ss") if not right(mypath, 1) = "\" mypath = mypath & "\" if not right(myfilename, 4) = ".xls" myfilename = myfilename & ".xls" wstrades.copy '<~~ why copy? don't see used? wb '<~~ explicit reference "mkl.xlsm" workbook .saveas filename:= _ mypath & myfilename, _ local:=true, _ fileformat:=xlworkbooknormal, _ createbackup:=false .close false end end if application.displayalerts = true end sub

in code above, , in own, should pay special attending line:

range("d10:cb10").copy

it implicitly implies range d10:cb10 in activesheet of activeworkbook. if working in different workbook, reference d10:cb10 in ever sheet active in workbook. while may not cause error uncertainty intended.

also, when save workbook referenced activeworkbook, 1 time again 1 working in.

i had issues line copies *.range("datenow:stock2"), have no thought should why have not tested code

excel vba excel-vba