Troubleshooting SharePoint workflow errors can be a real pain because the extent of your feedback is "Error in Workflow".  That's it.  Nothing in the event log, nothing in the page source and nothing useful in the Workflow history.  However, there is a way to get more detailed information about exactly what happened.

To get the real dirt on the error, you need to enable Diagnostic Logging so that it will write all the details about the error to a log in 12/LOGS.  Go to Central Admin -> Operations -> Diagnostic logging -> Event Throttling.  There you will be able to set how much information you want logged.  There is a tiny catch here though in that you can only change one setting at a time, so set the "Workflow Features" category as shown below and click OK
 

Now go back in and set the values for "Workflow Infrastructure" and click OK
 

Then run whatever process allows you to replicate the error.  Quickly jump into the 12/LOGS folder on the SharePoint server, and open the most recent log file.  The actual workflow error should now be visible towards the bottom of the log.