0

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?

Community
  • 1
  • 1
Robert Bursey
  • 1
  • **should mention that these UDF's iterate over tabs in the workbook** Well that depends on how you code that iteration. If sheet names are hard coded into the itteration chances might very well be that it breaks. – Luuklag Sep 15 '17 at 13:50
  • Is the workbook you're copying to a new workbook? If so, you could create a template workbook already containing the module and copy the sheets into it each time. Failing that this link seems to do what you're after: https://stackoverflow.com/questions/40947736/vba-copy-module-from-one-workbook-to-another (in which case this question is a duplicate). – Darren Bartrup-Cook Sep 15 '17 at 15:12
  • Thanks for the ideas. In the end, I took the easy way out and copied the sheets into the workbook that had the UDFs. I thought this would be more difficult in that there are up to 20 of these sheets, vs. just one if I copied the other way. But it was remarkably fast, I guess because each sheet is relatively small. I think this is a very satisfactory solution, so thanks very much – Robert Bursey Sep 21 '17 at 14:10

0 Answers0