Copying cells to Word causes UDF errors

[JenniferMurphy]

I have a fairly complicated workbook with 8-10 sheets used track health data such as weight & blood pressure. The workbook makes use of a few UDFs I wrote. Most of the time, only the "Active" sheet has active calls to these UDFs. The other sheets have been converted to plain text by copying them to themselves with the Copy Values option.

Every few weeks I make a new Active sheet by making a copy of it, copying the table in the copy to itself to convert it to plain text, then copying that and pasting it into a Word document. I then reset the new Active sheet and continue collecting data.

I have been doing this for over a year. From time to time, I will add some code to the UDFs, but it has always worked. Today, when tried to copy the table to the Word document, nothing happened. Word seemed to freeze. When I task switched back over to Excel, I got a MsgBox message from one of UDFs claiming it found bad data in one of the cells. This is strange because no UDFs are called from the sheet containing the table being copied. Moreover, the data in that cell appears to be good. At least I don't get an error any other time -- just when copying the table. When I returned to Word, I discovered that the table had been copied. I tried it several times With the same results.

So I put a breakpoint on that UDF and tried again. Same result. Word froze up and when I switched back to Excel, I got the same messages, but the breakpoint did not stop execution. I then put breakpoints on the MsgBox statements. No luck. No breaks.

Two questions:

1. Can anyone suggest what might be going on? And why won't the breakpoints work?

2. The MsgBox reports the row and column of the cell it think is bad, but it does not tell me the sheet. Who do I retrieve the name of the calling sheet inside the UDF so I can add that to the UDF?

Thanks

 

[JenniferMurphy]

PS: If I change the sheet from Automatic to Manual, the error messages do not occur and the copy completes normally.