VBA/Paste Special Error

McDan1el

Board Regular
Joined
Sep 14, 2007
Messages
89
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

VJ1985

New Member
Joined
Aug 1, 2013
Messages
8
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?

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

Watch MrExcel Video

Forum statistics

Threads
1,108,600
Messages
5,523,821
Members
409,538
Latest member
husnafezaahmad

This Week's Hot Topics

Top