I have a workbook with 5 sheets full of UDF (user defined functions).
I need to copy these 5 sheets to a new workbook but I only need the values.
The problem is that when I copy these sheets all cells with UDF became broken with #value on it, because I don't have my macros on this new workbook.
I cannot copy the module, because many users will use this workbook and maybe their Excel will not allow me to manipulate the vba project.
I tried copying and pasting only values between workbook but it doesn't work. I think that is because my sheet have a lot of merged rows and columns. Excel shows an error message. I'll translate from portuguese to english here, maybe Excel doesn't use this exactly workd:
This option requires that merged cells are all the same size.
I don't understand this error. I even tried to do this manually: copy the entire sheet (right click, move and copy), then selected all the used range on my original workbook, ctrl+c, then selected the first cell in the new workbook then ctrl+v and then paste only values. And Excel throws an error. I also tried selecting the entire range on the new workbook before pressing ctrl+v. Same problem.
I don't know what to do. What I am doing now is copying and pasting only values in my original workbook and then copying the entire sheet to a new workbook. The problem is that this action destroy my original workbook, so I have to close it and reopen, what I didn't want to do.
Anyone have any idea on how to proceed?
Try these steps