Hey all 
,
Quite regularly in our company we need to copy a worksheet from one workbook to another. In the source book this sheet contains many references to cells from other sheets in the same (source) book.
However, when copying the worksheet to a destination workbook I need to prevent the occurence of links to the source workbook, and prefferentially only have values copied over (this destination workbook is the final archive for all the many different source sheets we produce over time and can thus be entirely static). Broken links usually shouldn't matter that much, and you would still see the last known data for the cells, but for some reason the people that use the destination file downstream of me manage to completely get the destination file corrupted if there are any references remaining in it (not sure what they do with it to cause this, but it needs to stop happening..).
I know there is the most simple option to right click and paste values, but my colleagues are unteachable, and have a hard time changing habbits. On top of this we do this copying twice weekly, different colleagues every time. It only has to go wrong once to get the whole file corrupted and losing the archive destination file (my boss ain't to fond of me when this happens more often), so I need to foolproof this system.
I have already locked and hidden the formulae/references in the sheet from the source file, preventing them being copied by a regular copy-paste action.
However another stubborn colleague apparantly copies the sheet to the new workbook by having both workbooks open, right clicking the sheet to copy and then hit 'move or copy' and copies it to the end of the destination workbook like that. This copies over the references and creates links, despite the thing being locked, hidden and whatnot.
How to prevent this from happening?
I've been looking into solutions doing either one of these things, but with none my searches on this I managed to get a solution, but perhaps they inspire you folk when thinking about it:
If you have any ideas how to solve this with any of these strategies, or another strategy please let me know.. I'd be eternally greatfull!
If the only solution is to get my colleagues to do it right, I'll start clicker training them with chocolate.. not sure what will be easier
Cheers,
Vera
,Quite regularly in our company we need to copy a worksheet from one workbook to another. In the source book this sheet contains many references to cells from other sheets in the same (source) book.
However, when copying the worksheet to a destination workbook I need to prevent the occurence of links to the source workbook, and prefferentially only have values copied over (this destination workbook is the final archive for all the many different source sheets we produce over time and can thus be entirely static). Broken links usually shouldn't matter that much, and you would still see the last known data for the cells, but for some reason the people that use the destination file downstream of me manage to completely get the destination file corrupted if there are any references remaining in it (not sure what they do with it to cause this, but it needs to stop happening..).
I know there is the most simple option to right click and paste values, but my colleagues are unteachable, and have a hard time changing habbits. On top of this we do this copying twice weekly, different colleagues every time. It only has to go wrong once to get the whole file corrupted and losing the archive destination file (my boss ain't to fond of me when this happens more often), so I need to foolproof this system.
I have already locked and hidden the formulae/references in the sheet from the source file, preventing them being copied by a regular copy-paste action.
However another stubborn colleague apparantly copies the sheet to the new workbook by having both workbooks open, right clicking the sheet to copy and then hit 'move or copy' and copies it to the end of the destination workbook like that. This copies over the references and creates links, despite the thing being locked, hidden and whatnot.
How to prevent this from happening?
I've been looking into solutions doing either one of these things, but with none my searches on this I managed to get a solution, but perhaps they inspire you folk when thinking about it:
- As soon as data is filled in a cell, the cell will itself become that static value (can be at source, but I prefer other methods since the source might have to be adjusted a few times before finally copying it to the destination.
- Source book does not allow any references to be copied out of it (managed this partly by hiding, but doesn't solve the 'move or copy worksheet to new document' method)
- When the sheet is copied to the new workbook, all links will break automatically/values are automatically pasted as values (can be either manipulated at source or destination, I have access to both to manage these things)
- Every time the destination workbook is saved, it automatically breaks all links, or reverts all formulae/references/links to static values (the destination folder has no references or whatsoever that should be kept at all!)
- Saving the destination file in a format that does not support links or formulae, effectively transforming all to static values (the workbook contains multiple formatted sheets, so .txt is not really an option)
If you have any ideas how to solve this with any of these strategies, or another strategy please let me know.. I'd be eternally greatfull!
If the only solution is to get my colleagues to do it right, I'll start clicker training them with chocolate.. not sure what will be easier
Cheers,
Vera
