excel - VBA: Finding a sheet by name across workbooks and copy a sheet next to it -

i kind of lost workbook reference , nesting if condition. want achieve is,

1. find workbook has sheet name "employee".
2. check there sheet name "company" in above workbook, delete if exist.
3. copy sheet "company" active workbook (not active sheet) above workbook.

i reaching far not sure how copy non-active-sheet "company" active workbook workbook find in point 1.

any suggestion appreciated.

    application.displayalerts = false     each wb in application.workbooks        each ws in wb.sheets        if ws.name = "employee" wb.sheets("company").delete        next ws     next wb     application.displayalerts = true 

by active workbook mean 1 code module running operation? if case, thisworkbook can used reference parent of company worksheet wish copy workbook deleted company worksheet from.

   application.displayalerts = false    on error resume next     each wb in application.workbooks         each ws in wb.sheets             if ws.name = "employee"                 wb.sheets("company").delete                 thisworkbook.sheets("company").copy before:=wb.sheets(1)             end if         next ws     next wb     application.displayalerts = true 

i find thisworkbook more definitive activeworkbook particularly in operations focus may (by default) changed workbook receiving copy of company worksheet.

alternately, can assign worksheet object reference company worksheet wish copy across , use reference repeatedly progress through workbooks collection.