excelvbaexcel-2010

Insert and Paste Values to a protected sheet


This Excel VBA code is supposed to copy the entries from Sheet "Entry" then Insert & Paste only Values in Sheet "List".

Sheet List has a password to prevent editing.

Sub ShopeEntry()
    
    Sheets("Entry").Select
    Range("D5").Select
        
    If IsEmpty(Selection.Value) = False Then
        
        Sheets("Entry").Select
        Dim last_row As Long
        last_row = Cells(Rows.Count, 4).End(xlUp).Row
        Range(Cells(5, 3), Cells(last_row, 10)).Select
        Selection.Copy
        
        Sheets("List").Select
        Selection.Protect Password:="Password", UserInterfaceOnly:=True
        
        Sheets("List").Select
        Range("C2").Select
        Selection.Insert Shift:=xlDown
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        Sheets("Entry").Select
        Range("D5").Select
        
    End If
        
End Sub

When I run this code an error message shows up at the Paste Special command.

Run-time error '1004': Select Method of Range Class Failed


Solution

  • Microsoft® Excel® for Microsoft 365 MSO (バージョン 2510 ビルド 16.0.19328.20190) 64 ビット

    I have preserved the logic of the question as much as possible.

    I have changed it to copy just before pasting.

    Sub ShopeEntry()
    
        Dim wsEntry As Worksheet
        Dim wsList As Worksheet
        Dim rngSource As Range
        Dim last_row As Long
        
        Set wsEntry = Sheets("Entry")
        Set wsList = Sheets("List")
        
        If IsEmpty(wsEntry.Range("D5").Value) = False Then
        
            With wsEntry
                last_row = .Cells(.Rows.Count, 4).End(xlUp).Row
                Set rngSource = .Range(.Cells(5, 3), .Cells(last_row, 10))
            End With
            
            With wsList
                .Protect Password:="Password", UserInterfaceOnly:=True
                .Range("C2").Insert Shift:=xlDown
                rngSource.Copy
                .Range("C2").PasteSpecial Paste:=xlPasteValues
            End With
            Application.CutCopyMode = False
            With wsEntry
                .Select
                .Range("D5").Select
            End With
            
        Else
    
        End If
        
    End Sub