I have a situation where multiple document libraries exist, and when any document in any library is published and approved, the document should then be copied to a "Public" library. I managed to do this using SharePoint Designer, and creating a custom workflow that activates when an item is changed. The workflow checks to ensure that the document is not checked out, that the version number ends in ".0" (so is a major/published version), and that the document's approval status is "Approved".
However, the problem lies with the fact that when SharePoint workflow is designed to copy the list item to another list/library, but an item with the same name exists, the default action taken is to create a new copy, with the date and time appended to the new item's name. I don't want this. So I've found this article which involves manually editing the workflow's XOML file. It's written for SharePoint Designer 2007 but I didn't have any problems carrying out the same function in SharePoint Designer 2010.