VBA/Paste Special Error

[McDan1el]

Hi Guys,

Trying to build a form where on it selects the data entry row of "d7" until it detects a blank and copy and paste that into a "log" sheet. It then checks for the next blank row and will paste - when the code runs it proves runtime error 1004 which says something about the paste range? If i "end" the debug and rerun the macro to paste and apply it works fine?!

This is the segment the debugger highlights from below:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


ActiveSheet.Unprotect

Range("D7").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Log").Select
ActiveSheet.Unprotect
Range("B65536").Select
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I65536").Select
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection = Application.UserName
Range("A1").Select
ActiveSheet.Protect
Sheets("Entry Sheet").Select
Application.CutCopyMode = False
Range("D7").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("D7").Select

ActiveSheet.Protect




Many Thanks

 

[McDan1el]

I love you both

Many Thanks - worked great!

 

[VJ1985]

I'm having a similar issue, but I do not have control over where the user copies from (usually an external source like a browser, another workbook, etc.). So I used the below code in my sheet and assigned CTRL+V to the macro. It works fine when the sheet is unprotected, but throws an error if protected.

I don't want users to modify the locked cells in my sheet, so I want it protected at all times. Is there anyway to accomplish this?

Sub PasteValuesOnly()
    If ThisWorkbook.Name = "INPUT.xlsm" And ActiveSheet.Name = "INPUT" Then
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Else
        MSGBOX "Please close the 'INPUT' spread sheet before performing this action.", vbOKOnly, "Operation Cancelled"
        Exit Sub
    End If
End Sub