Copying and pasting

[Throughstream]

Hi I have one document which is constantly being updated. I need to copy and paste from that document to another without copying the formulae. I know how to do it by clicking and pasting special but is there a way to get the first document to only allow me to copy the value each time rather than the formulae.

Cheers

Dave

 

[Eric W]

How is the sheet being updated? If you format the cells, from the Protection tab check Locked and Hidden, then protect the sheet from the Review tab, allowing the Select Locked Cells option, then you can copy the range and it will paste only the values.

But that could cause problems, since you won't be able to manually change that sheet. But if the sheet is being changed by a macro, then you could use the UserInterfaceOnly option which allows a macro to change the sheet, but not the user.

If you just want a faster hot-key way to paste values, check this out:
https://support.office.com/en-us/ar...ues-only-a39d9511-db8a-41f0-8127-b7af7d8e813e

 

[Throughstream]

Hi certain values are entered and a formulae works out a value and the whole lot is copied over. Is there any way on the receiving document to only allow values and not formulas?

Cheers

Dave

 

[Eric W]

On the receiving side, you'd need to use Paste Values. On the sending side, you could format all the cells as Unlocked, Unhidden. Then select the cells with the formulas in them and form them those as Locked/Hidden. Then protect the sheet, allowing locked cells to be selected. With or without a password. Then when you copy from that sheet, only values from the locked/hidden cells will be pasted.