That might work if the VBA logic is embedded in the worksheet's code module. But his code is in normal VBA modules. To copy those requires a lot more work—but it can be done.
I have a client with a large complex workbook. I'm in North Carolina, he's in Bermuda. He used to send me the workbook so I could develop code for it, and while it was in my hands he had to wait until I'd sent it back with the new VBA code before he could make any changes in the data. Sometimes very inconvenient for him. So I played around until I'd found a way to copy code modules from one workbook to another. Now I can develop the programs at my end while he makes changes in his content; when I'm ready to update his programs he sends me the worksheet, I run a program that replaces all the modules, then I send the result back and his data is ready to work on again in only that amount of time.
If there's a way to copy code modules directly, I haven't found it yet. What my program does is a) establish the source and target workbooks, b) create a new folder, c) export all the modules from the source workbook to the folder, d) delete all the modules from the target workbook and e) import all the modules from the folder into the target workbook. Your situation sounds a lot simpler, but drilling down to the modules is still a bit of work. I wrote a Modules class to handle all these details, but the basic steps look like this:
1) Establish in your program the source and target workbooks. The source is the one your module(s) are going to be copied from; the target is the workbook you're going to copy them to.
2) Set ovbe = Application.VBE
I couldn't find documentation on the VBE object so I had to look in a VBE watch window to figure out some of what follows. But it works:
3) For the source project, cycle through ovbe.VBProjects until I find the one with the right FileName property. The FileName is identical to the source workbook's path and filename.
4) Do the same for the target project.
You now have the source and target project objects. Let's call them oprjFm and oprjTo.
5) Cycle through the oprjFm.VBComponents collection. Each VBComponent object is a module of some kind. At this point my program filters out a few modules by looking at each VBComponent's Type and Name properties; you probably don't need to do that. My program loaded the desired modules (the VBComponent objects) into a collection for subsequent handling; again, maybe you don't need to.
6) Create a new file folder. I always name it "Modules yyyy-mm-dd" so it doesn't clash with previous runs, but that's up to you. Oh, and I use the FileSystemObject routinely for tasks like this, but if you have your own favorite method for this kind of thing do it your own way.
7) For each module that I stored in the source collection in step 5, use the module's Export method, supplying a file name that includes the path to the file folder and an extension that matches the module type: ".bas" for Type 1 (regular code modules), ".cls" for Type 2 (class modules). Type 3 is a form module; I'm still experimenting with those.
8) This is where my program deletes all the modules in the target workbook, but that doesn't apply to your situation I gather.
9) You're about to run through all the files you exported to that file folder, so establish a pointer to it; use the FileSystemObject, as I do, or however you prefer if you like something else.
10) So far you've been working mostly with oprjFm, that is, the source project. Now go back to oprjTo, the target project that you created in step 4. Set ocmps = oprjTo.VBComponents. It's empty, but it has an Import method. Cycle through all the files in the folder and use ocmps.Import to import the module(s), specifying the full path and filename as the argument to the method.
I think that's it. I mean, my program saves the target workbook and drops a message about how many modules were copied, along with a reminder to do something else manually, but that's it for you. It's a lot of steps and few of them are terribly obvious, but it seems to work for me.
