excel - Mismatch Error when adding a new workbook -

update #2: have split line set excelbook = workbooks.add 2 separate lines (workbooks.add , set excelbook = activeworkbook) , still randomly receiving error. not happen every time, when directs me line workbooks.add (still receiving same type mismatch error).

sub newworkbook(companyname string, outputdirectory string, scenario string)  dim excelbook workbook dim copyarea range  set copyarea = range("copyarea") workbooks.add set excelbook = activeworkbook  copyarea.copy  excelbook.sheets("sheet1").range("a1").pastespecial xlpastevalues excelbook.sheets("sheet1").range("a1").pastespecial xlpasteformats excelbook.sheets("sheet1").columns(2).entirecolumn.delete excelbook.sheets("sheet1").rows(6).entirerow.delete excelbook.sheets("sheet1").cells.entirecolumn.autofit  application.displayalerts = false excelbook.close savechanges:=true, filename:=outputdirectory + "\" + replace(replace(replace(companyname, "\", ""), "/", ""), ":", "") + " - " + scenario + ".xlsx" application.displayalerts = true  set excelbook = nothing set copyarea = nothing              end sub 

update: have re-worked code remove select/activate, still getting random mismatch error. when receive error, seem creating new workbook, stops after (after receiving error , selecting 'end', there blank workbook not there before).

original description: receiving mismatch error in above code on line 'set excelbook = workbooks.add'. strange error not occur every time (i can't seem find pattern on why happens , not others).

i have macro calls below code in 'for' loop. finish successfully, other times error out on 1st iteration, other times on 5th iteration, etc.

any thoughts?

the error due lack of specification on range assignment, change:

set copyarea = range("copyarea") 

to

set copyarea = thisworkbook.sheets(1).range("copyarea") 

or change number sheet's name.