I am trying to copy a sheet from one file to another and get the VBA to come along with it. The source sheet has formulae, which are based on UDF's. They are located in a module in the source book.
When I copy the sheet, the module does not come along to the destination file, nor does the code. So I tried putting the code in the sheet of the source file. Now the code does indeed come along, but the UDF's don't work. I get (I think) #NAME? errors, or something like that. I should mention that these UDF's iterate over tabs in the workbook if that's relevant. Does it sound right that sheet level code won't work in this case? I have never put VBA anywhere but modules before.
If sheet code won't work, I am stuck with a module. I realize I can export Module1 from the source file, and then import to the destination. The source module should never change, so I can do this one time only. Does this sound like the most painless solution?
I am trying to avoid a macro, unless it's short and simple. Unlike the source file which is non-changing, the destination file name changes daily, i.e. every time it is opened it needs to recieve the module (and sheet). Does that sound doable?