Copy into partially protected excel sheet

[Zunit]

Hi,

I have an excel sheet with certain cells protected which I sent out to various branches/divisions to complete. They could only complete the unprotected cells.

Now I want to copy these sheets all into one workbook. This workbook should have separate tabs for each of the divisions sheets. This workbook also has the same format as the worksheets I want to copy from (with the same cells protected also).

I want to copy one worksheet at a time into the consolidated workbook. But excel doesn't allow me to do this because some of the cells are protected on both sides.

If I were to copy each unprotected cell at a time it would take forever to do. And I don't have the password to unlock the sheets/workbook.

Please help.

 

[Jerry Sullivan]

Hi Zunit,

If the protection is at the worksheet level only, and the workbooks are not protected, you could copy each entire sheet into a new blank workbook instead of trying to transfer the values from the unlocked cells of one worksheet to another.

If the workbook itself is protected, the process you describe could be done with a VBA macro.
Unless you are working in a disfunctional organization, I'd first try to work with the owner of the protected workbook to either unprotect the workbook level, or share the password with those with a need to process this information.

 

[Zunit]

Hi,

Thanks for your response. The whole workbook is locked. Can you assist with the VBA code? I don't know how to write them.

i can't get the password since the document is locked so that users at my level can not modify the formulae in the locked cells. I just need a quick way of copying the unlocked cells in without having to copy one cell at a time.

Let me know if you want a sample of the workbook.

Thanks.

 

[Jerry Sullivan]

Hi, I'll be glad to look at your file(s) and I've sent you a PM with my email address.

The owner of the workbook could unprotect the workbook and leave the individual worksheets protected and it would still prevent users from modifying the formuae in the locked cells. I understand that you might not be in a position to have that change made, but it's unfortunate that you are having to work around this obstacle imposed by someone in your same organization. Hopefully, if that person understood the workbook protection is not necessary to prevent data from being entered into locked cells, they would want to make it easier for you to do your job well.

Is the workbook that you want to copy the data into also protected at the Workbook level?
If so, that presents a problem, because even though you can use VBA to transfer data from the unlocked cells in one workbook to the unlocked cells in another target workbook, you won't be able to copy that blank template sheet within the target workbook.

Please send an example of both a source workbook to copy from, and the target workbook you want to copy to and we'll discuss some available options in this thread.

 

[Jerry Sullivan]

Thanks for sending the source and target files.

The workbooks are actually not protected at the workbook level - only at the worksheet level.
This would allow you to copy worksheets from each branch/division into a single workbook without the need to transfer the data from the unlocked cells of one sheet to another.

The approach would be much faster, reliable and adaptable to changes - the only problem is that your the workbook has two sheets "Segmental" and "Consol" which have formulas referencing the existing sheets in both the target and source workbooks.

Depending on how you need to use this, one option would be to make your own replacement versions of those two summary sheets using the same format and formulas except they would erence your copied sheets instead.

I'll can help with either that approach or the cell by cell copy macro- just let me know your preference.

 

[Jerry Sullivan]

Below is some VBA code that you can try to copy the cells from one worksheet to another.

I planned to build a range of all unlocked cells; however in looking further at your workbook there are many unlocked cells that should not be copied. It appears that the data entry cells that you want to copy all share the same Fill Color. If that's consistent for your use, then we can copy all the cells having that color.

Here's some instructions on how to set this up and run a test....

1. Open the two workbooks you will be using to Copy and Paste.

2. Start a new workbook and name it "CopyByColor.xlsm"

3. Press the keys ALT + F11 to open the Visual Basic Editor

4. Press the keys ALT + I to activate the Insert menu

5. Press M to insert a Standard Module

6. Copy the code below and paste it into the Standard Module

7. Download Chip Pearson's Color Functions module using this link:
http://www.cpearson.com/Zips/modColorFunctions.zip

8. Extract the file modColorFunctions.bas from the zip file, then drag and drop onto the ThisWorkbook icon in the VBA
Project explorer to add it to the project.

9. Press the keys ALT + Q to exit the Editor, and return to Excel

10. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog.

11. Double Click the macro's name "CopyTest" to Run it.

Sub CopyTest()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim vReturn As Variant

    
    Const ColorIndex As Long = 19
    Application.ScreenUpdating = False

    
    Set wsSource = Workbooks("[COLOR="#0000CD"]SBD.xlsm[/COLOR]").Sheets("[COLOR="#0000CD"]Silk by Design[/COLOR]")
    Set wsTarget = Workbooks("[COLOR="#0000CD"]Rental and Products - Budget 2014.xlsm[/COLOR]") _
        .Sheets("[COLOR="#0000CD"]Silk by Design[/COLOR]")
    vReturn = CopyByColor(wsSource, wsTarget, ColorIndex)

    
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    If vReturn <> vbNullString Then MsgBox vReturn

    
End Sub

Function CopyByColor(wsSource As Worksheet, wsTarget As Worksheet, _
        ColorIndex As Long) As Variant
'---Copies Values from all cells with specified Interior.ColorIndex in Source Worksheet
'      to same range cells in Target Worksheet
'   Returns "" if no errors else a string with an error message.
'   Requires Chip Pearson's modColorFunctions module to be added to workbook.
'      download at: http://www.cpearson.com/Zips/modColorFunctions.zip

    Dim rSource As Range
    Dim i As Long

    
    On Error GoTo ErrHandler

    
    With wsSource
        Set rSource = RangeOfColor(TestRange:=.UsedRange, _
                ColorIndex:=ColorIndex)
        If Not rSource Is Nothing Then
            For i = 1 To rSource.Areas.Count
                With rSource.Areas(i)
                    .Copy
                    wsTarget.Range(.Address).PasteSpecial (xlPasteValues)
                End With
            Next i
        End If
    End With

    
    Exit Function
ErrHandler:
    CopyByColor = "An error occurred while copying range: " & _
        rSource.Areas(i).Address
End Function

If you are copying to and from workbooks and worksheets other than the names shown in blue font in the code, you'll need to modify that part of the code to match the actual names.

Just ask if you have any difficulties getting that to work.

 

[Zunit]

Wow! Very impressive! Its worked perfectly. Thanks very much.

 

[nikki1]

Hi
</SPAN>
Hope you can help with my query.

I am in need of a similar solution. I am trying to copy data from one model in to another (there has been a revision to the existing model I have completed resulting in the release of another). The model consists of a number of worksheets with a combination of protected and unprotected cells. The unprotected cells are of yellow colour and therefore I attempted the above mentioned script (editing the references to my workbooks and worksheets).

This is causing an error 9, and highlights the workbook reference, which I believe appears correct.

In addition I have saved the modColorFunctions.bas document to my desktop and tried to copy it in to VBA not sure if this is the correct way. I have also tried to run this as a VBA but the same error appears. Which part of VBA is the project explorer section as it won’t allow me to drag it anywhere in that screen.

Hope this makes sense. </SPAN>
Thanks</SPAN>

 

[Jerry Sullivan]

Hi nikki1,

Which statement is highlighted when you get that error message? What is the error description shown with Error 9?

The Project Explorer should look like this after you drag and drop the modColorFunctions.bas from your desktop onto the "ThisWorkbook" icon.

As an alternative to the drag and drop, you could right-click on any of the ThisWorkbook icon then "Import file..." then browse for the .bas file.

 

[nikki1]

Hi Jerry

The error that is highlighted is the ws source statement. The first item is the workbook and the second is the worksheet within this that needs to be copied.

Set wsSource = Workbooks("LTFM Model v.1.xlsm").Sheets("I_Incme (Base)")

What I'm trying to do is copy all non protected cells which are yellow in colour to a newer version of the same model.

Thanks
Nikki

Hi nikki1,

Which statement is highlighted when you get that error message? What is the error description shown with Error 9?

The Project Explorer should look like this after you drag and drop the modColorFunctions.bas from your desktop onto the "ThisWorkbook" icon.

As an alternative to the drag and drop, you could right-click on any of the ThisWorkbook icon then "Import file..." then browse for the .bas file.