Copy/Paste Run Time Error 1004

[Lost Paul]

I'm trying to reduce user errors by coding a copy/paste process.

Excel is not liking something, but I know not what.
The following error comes up;

Run time error '1004':
PasteSpecial method of Range Class failed

I've stuck to xlPasteValues.
The target range is the same size as the copied range size.
I've tried without the 'protection.
Here's the sub;

Code: Select all

Sub MainData()
'
' Copy Paste the entries to the OCC tab
'
    Dim SceNo As Integer
    Dim RowNo As Integer
    Sheets("Input").Select
    'Sheets("Input").Unprotect ("******")
    SceNo = Range("P2") + 6

    Range(Cells(7, "B"), Cells(SceNo, "M")).Select
    Selection.Copy
    Sheets("OCC").Visible = True
    Sheets("OCC").Select
    'Sheets("OCC").Unprotect ("*********")

    Range("B6:R6").Select
    Selection.AutoFilter
    Range("B7").Select
    
    RowNo = Range("O2").Value
    Range(Cells(RowNo, "D"), Cells((RowNo + (SceNo - 7)), "O")).Select ' PasteSpecial Paste:=xlPasteValues ◄ error line

    'Sheets("OCC").Protect ("********")
    
End Sub

Any ideas?

[HansV]

Does this work?

Code: Select all

Sub MainData()
'
' Copy Paste the entries to the OCC tab
'
    Dim Src As Worksheet
    Dim Trg As Worksheet
    Dim SceNo As Integer
    Dim RowNo As Integer
    Set Src = Sheets("Input")
    Set Trg = Sheets("OCC")
    'Src.Unprotect ("******")
    SceNo = Src.Range("P2") + 6
    Src.Range("B7:M" & SceNo).Copy
    'Trg.Unprotect ("*********")
    RowNo = Trg.Range("O2").Value    
    Trg.Range("D" & RowNo).PasteSpecial Paste:=xlPasteValues
    'Trg.Protect ("********")
End Sub

[Lost Paul]

That's the ticket.

I haven't looked at the differences yet, but what mistake(s) did I make?

Thank you.

[HansV]

I'm not sure, but my version avoids selecting ranges; that usually works better.

[Lost Paul]

Curious.

I did give that a go too.
Hey ho.

Thank you again