How copy/paste values in Excel doc protected sheet 2 other doc?

[Got my CWC]

How can I copy the values from a protected sheet in an Excel doc without
unprotecting the sheet? I want to be able to paste the values into another
doc. Any ideas? THANKS!

 

[JLatham]

Easiest way is to simply unprotect the source sheet:
Tools | Protection | Unprotect sheet
But if it's password protected, that may not work.

Alternative method:
Open both workbooks.
Go to the cell where you want the information to show up in and type the
= symbol. Then go to the other book/sheet where the information is in the
protected sheet and click in the proper cell. Hit the [Enter] key. Done

Now you can go one step further if you want to remove the Link from the copy
back to the original (protected) workbook: Select all of the cells and use
Edit | Copy
and turn right around without unselecting them and use
Edit | Paste Special and click the "Values" option in the Paste Special
dialog. Then finish the operation. The formulas will be changed to the
displayed values and the links to the original source data will be removed.

 

[Got my CWC]

The workbook has bee protected to not allow cell selection. Any other ideas?

Easiest way is to simply unprotect the source sheet:
Tools | Protection | Unprotect sheet
But if it's password protected, that may not work.

Alternative method:
Open both workbooks.
Go to the cell where you want the information to show up in and type the
= symbol. Then go to the other book/sheet where the information is in the
protected sheet and click in the proper cell. Hit the [Enter] key. Done

Now you can go one step further if you want to remove the Link from the copy
back to the original (protected) workbook: Select all of the cells and use
Edit | Copy
and turn right around without unselecting them and use
Edit | Paste Special and click the "Values" option in the Paste Special
dialog. Then finish the operation. The formulas will be changed to the
displayed values and the links to the original source data will be removed.

How can I copy the values from a protected sheet in an Excel doc without
unprotecting the sheet? I want to be able to paste the values into another
doc. Any ideas? THANKS!

 

[Gord Dibben]

You can allow users to select locked and unlocked cells on a protected
sheet.

If you can select.........you can copy and paste to another sheet.


Gord Dibben MS Excel MVP

 

[JLatham]

A little more effort is required. You'll have to hand type in the
formula(s). Have both workbooks open. I'm assuming its a separate book.
For the following formula, let us say that the protected sheet is in
OtherBook.xls and the sheet name is [Protected Sheet] and we want to start
with a copy of the value in cell X7.

In your workbook put this in a cell as a formula:
='[OtherBook.xls]Protected Sheet'!X7
By leaving the cell reference relative (instead of making it absolut by
using $X$7), you can now drag that formula to fill in more cells to pick up
other values on that sheet in OtherBook.xls.

Once you're done, you can still do the Edit | Copy and Edit | Paste Special
(Values) to lock the values in place and remove the links to the source file.
Or not - if you don't, then as the referenced cells in OtherBook.xls, sheet
Protected Sheet change, they'll get those changes reflected when you open
this book.

Also, if you don't break the links, the formulas will even 'auto update' to
show the full path to the OtherBook.xls file. While Otherbook.xls is closed,
the formula will show full path to it, when it's open with this one, they
will just show the OtherBook.xls filename as part of the formula.

Hope this helps.

The workbook has bee protected to not allow cell selection. Any other ideas?

Easiest way is to simply unprotect the source sheet:
Tools | Protection | Unprotect sheet
But if it's password protected, that may not work.

Alternative method:
Open both workbooks.
Go to the cell where you want the information to show up in and type the
= symbol. Then go to the other book/sheet where the information is in the
protected sheet and click in the proper cell. Hit the [Enter] key. Done

Now you can go one step further if you want to remove the Link from the copy
back to the original (protected) workbook: Select all of the cells and use
Edit | Copy
and turn right around without unselecting them and use
Edit | Paste Special and click the "Values" option in the Paste Special
dialog. Then finish the operation. The formulas will be changed to the
displayed values and the links to the original source data will be removed.

How can I copy the values from a protected sheet in an Excel doc without
unprotecting the sheet? I want to be able to paste the values into another
doc. Any ideas? THANKS!