vba code to copy a specific sheet from a closed workbook to the active workbook

Why is it important to you that the source file remain closed?

I dont want to say it cant be done, as a matter of fact I know it could be done...its just extremely difficult and not worth the effort. Generally when doing this you open the source file, copy what you need and close it in the macro. The end user never has to see the file or know its been open. If I know all I need is to get info from it I even open it as read-only so my code doesn't accidentally change the source file.

The only way I can think of you will accomplish this without opening the file is to (always in a copy, not the original file) change it to a zip, extract the xml representing the sheet and additional information needed, incorporate it into your own file: generally destination also needs to be closed to directly insert the xml parts/manipulate the underlying xml) or you parse the underlying xml from the source and process it into a new sheet you create. If this sounds complicated and way overboard...thats because it would be.

Once we know why opening the source isnt an option (or maybe it really is) we can more directly answer your question

The file need to be closed because it is a template sheet with formula, I have some users who messed the template file and it was a headache .

So this is why it is important the file remain closed as it is will be triggered by a macro.