0

The workbook that I am using in Excel has some protected cells. I am not able to unlock them. I need to transfer data from one column that is locked, to another column that is also locked. I can do this manually by typing in the data, but the workbook will not allow me to copy/paste (either using VBA or the normal way) the data in the protected column. Below are examples of the code that I've tried, without any luck.

Is this even possible without unlocking the workbook?

Example 1:

Sub Requote()

Dim JDsheet As Worksheet
Set JDsheet = ActiveWorkbook.ActiveSheet

JDsheet.Range("Q26:R41").Copy JDsheet.Range("S26:T41")

Example 2:

Sub Requote()

Dim JDsheet As Worksheet
Set JDsheet = ActiveWorkbook.ActiveSheet

JDsheet.Range("Q26:R41") = JDsheet.Range("S26:T41").Values

Note: The columns I am working with are made up of merged cells.

Edit: A comment from @Raystafarian led me to a post here which allowed me to unlock the protected sheet.

I will leave the question open for anyone who has a solution other than a hack on the protected workbook.

Community
  • 1
  • 1
bdkong
  • 181
  • 3
  • 9
  • 21
  • I am guessing no and your attempts confirm it. Why don't you just add unlock at top of code then lock at end? – findwindow Jul 31 '15 at 16:59
  • @findwindow The workbook that I am using in is not owned by me. Merely a template for a business transaction. I don't have the power to unlock the cells as far as I know. – bdkong Jul 31 '15 at 17:01
  • You have the workbook so you can crack the password =P But ok, like above, I am not 100% but I am guessing no :/ – findwindow Jul 31 '15 at 17:09
  • Hmm when you crack it, it may find "another" password so not sure if that's usable in code. Never tried XD – findwindow Jul 31 '15 at 17:14
  • 2
    [just unlock it](http://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project) – Raystafarian Jul 31 '15 at 17:48
  • Your code is working fine with me... merged cells or no cells... Is it possible for me to see the workbook. you can delete the rest of the sheets and just leave this particular sheet and upload it in any free file sharing site and share the link here? – Siddharth Rout Jul 31 '15 at 18:03
  • 1
    BTW `JDsheet.Range("Q26:R41") = JDsheet.Range("S26:T41").Values` should be `JDsheet.Range("Q26:R41").Value = JDsheet.Range("S26:T41").Value` – Siddharth Rout Jul 31 '15 at 18:14
  • Thank you for that link @Raystafarian. I genuinely did not think I could unlock it. I will leave the question open in case someone has a solution that doesn't resort to unlocking the sheet. – bdkong Jul 31 '15 at 18:57
  • If you can get client to reprotect, see [option two](http://stackoverflow.com/questions/11981410/unprotect-sheet-with-password-without-exposing-password-in-the-macro) – findwindow Jul 31 '15 at 21:28

1 Answers1

0

Try the following:

  • in a (new) or open sheet that is not protected, retrieve the values simply by referring the to the protected cells. This works for me.

e.g. "=Sheet1!C3" in an unprotected cell, where cell C3 in Sheet1 is the protected cell. You can drag the formula from there or use VBA to copy the formula in a large range.

After getting all the values, simply copy paste your formula's as plain values.

EDIT: if you have no choice but to insert the values in another protected column, then the answer is no i'm afraid.

Carlo
  • 33
  • 2