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

I have an active workbook with several sheets

I am looking a vba code that copy a specific sheet called "workings" from a closed workbook of the following path"c:\\sample\INTERFORMAT.xlsx" to the active last sheet in the open workbook.

That is sheet named "workings" should be last sheet in the active workbook but the INTERFORMAT.xlsx should always remain closed

Thanks if anyone can assist

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.

That may be a reason to ask people not to open the file themselves, but for a macro thats not really a valid reason to not open it via the macro, process it and then close it.

As I mentioned, I typically open a read-only copy of the file so that any potential changes cant be saved to it. Your users will never know its been opened and closed. There are multiple ways to accomplish opening and closing the file without displaying it to the user while the macro runs. You can use screen updating or open a new instance of Excel and setting its visible property to false then opening the file within the new instance.

Here is the code that is working smartly

Please Login or Register  to view this content.