Working Code Stops Working When Freeze Range Extended

[shellp]

Hello

With the help of Skip Vought I was able to create an Excel 2010 application. VBA code that I have running takes data from a worksheet(RawData_A) and each row of data is plugged into a single worksheet. I freeze some of the cells so that users can't enter information in those cells but I've decided I want to expand that zone to A1:H79, I5:I79, K5:P38, N40:P61, Q5:Q79.

There is also a Worksheet_Change event in the Template worksheet that also unfreezes and freezes cells.

If I just simply change the range to the above I get a run-time error 1004 "PasteSpecial method of Range class failed".

Can someone please advise what am I missing as something that needs to be done so I can freeze the ranges I want to? Thanks very much!

Sub AbstractData:

Sub AbstractData()</SPAN></SPAN>
Dim r As Range, wsAdd As Worksheet, t As Range, rSEQ_NO As Range, s As Range, myPassword As String, ws As Worksheet</SPAN></SPAN>
 
If worksheetexists("1") Then</SPAN></SPAN>
MsgBox "Abstracts have already been created"</SPAN></SPAN>
 
Else</SPAN></SPAN>
 
With Sheets("RawData_A")</SPAN></SPAN>
Set rSEQ_NO = .Rows(1).Find("SEQ_NO")</SPAN></SPAN>
 
If Not rSEQ_NO Is Nothing Then</SPAN></SPAN>
For Each r In .Range(.[A2], .[A2].End(xlDown))</SPAN></SPAN>
 
Sheets("Template").Copy After:=Sheets(Sheets.Count)</SPAN></SPAN>
Set wsAdd = ActiveSheet</SPAN></SPAN>
wsAdd.Name = .Cells(r.Row, rSEQ_NO.Column).Value</SPAN></SPAN>
wsAdd.Tab _</SPAN></SPAN>
.Color = 49407</SPAN></SPAN>
 
For Each t In [From]</SPAN></SPAN>
.Range(.Cells(r.Row, t.Value), .Cells(r.Row, t.Offset(0, 1).Value)).Copy</SPAN></SPAN>
wsAdd.Range(t.Offset(0, 2).Value).PasteSpecial _</SPAN></SPAN>
Paste:=xlPasteAll, _</SPAN></SPAN>
Operation:=xlNone, _</SPAN></SPAN>
SkipBlanks:=False, _</SPAN></SPAN>
Transpose:=False</SPAN></SPAN>
 
wsAdd.Unprotect</SPAN></SPAN>
wsAdd.Range("A5.J80").HorizontalAlignment = xlLeft</SPAN></SPAN>
wsAdd.Range("A5.J80").VerticalAlignment = xlTop</SPAN></SPAN>
wsAdd.Cells.Locked = False</SPAN></SPAN>
wsAdd.Range("A1:A79,C5:H38,F40:H61,I5:I79,K5:P38,N40:P61,Q5:Q79").Locked = True</SPAN></SPAN>
wsAdd.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True</SPAN></SPAN>
Next</SPAN></SPAN>
Next</SPAN></SPAN>
End If</SPAN></SPAN>
End With</SPAN></SPAN>
 
End If</SPAN></SPAN>
End Sub

</SPAN></SPAN>

Worksheet_Change Event:

Private Sub Worksheet_Change(ByVal Target As Range)</SPAN></SPAN>
    Dim t As Range, rng As Range</SPAN></SPAN>
   
    Set rng = Union( _</SPAN></SPAN>
        Intersect(Rows("5:37"), Range([b1], [j1]).EntireColumn), _</SPAN></SPAN>
        Intersect(Rows("40:60"), Range([b1], [m1]).EntireColumn), _</SPAN></SPAN>
        Intersect(Rows("63:79"), Range([b1], [p1]).EntireColumn))</SPAN></SPAN>
       
       
    Me.Unprotect</SPAN></SPAN>
    Me.Cells.Locked = False</SPAN></SPAN>
   
    For Each t In Target</SPAN></SPAN>
        With t</SPAN></SPAN>
       
        'is change in column J?</SPAN></SPAN>
            If Not Intersect(t, rng, Cells(1, "J").EntireColumn) Is Nothing Then</SPAN></SPAN>
                If t.Value <> Cells(t.Row, "B").Value Then</SPAN></SPAN>
                    .Interior.Color = 49407</SPAN></SPAN>
                  Else</SPAN></SPAN>
                    .Interior.ColorIndex = xlColorIndexNone</SPAN></SPAN>
                End If</SPAN></SPAN>
            End If</SPAN></SPAN>
       
       'is change in column K?</SPAN></SPAN>
        If Not Intersect(t, rng, Cells(1, "K").EntireColumn) Is Nothing Then</SPAN></SPAN>
                If t.Value <> Cells(t.Row, "C").Value Then</SPAN></SPAN>
                    .Interior.Color = 49407</SPAN></SPAN>
                  Else</SPAN></SPAN>
                    .Interior.ColorIndex = xlColorIndexNone</SPAN></SPAN>
                End If</SPAN></SPAN>
            End If</SPAN></SPAN>
 
        End With</SPAN></SPAN>
    Next</SPAN></SPAN>
        Set rng = Nothing</SPAN></SPAN>
       
    Me.Range("A1:A79,C5:H38,F40:H61,I5:I79,K5:P38,N40:P61,Q5:Q79").Locked = True</SPAN></SPAN>
    Me.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True</SPAN></SPAN>
   
End Sub

</SPAN></SPAN>

 

[AlphaFrog]

PasteSpecial All pastes the Locked setting the same as the copied cells. Just unlock column J after all the pasting is done.

 

[shellp]

Thanks! I'm pleased with myself because I figured that out before you responded!!

I don't know if I should start another thread on this or not but I also set my scroll range on the worksheet but if I scroll down to that area and it loops around i.e. starts at the beginning again, it seems to unlock the locked cells so I can now edit them. Does this make sense?

Also, when in the workbook if I go to the "information about" page I see under permissions whatever worksheets are visible Unprotect underlined beside it. I can select unprotect and it does....the workbook is password protected so do I also need to password protect each worksheet? Thanks.