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
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