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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What is the exact text of the error message? Do you have any merged cells in the copy or paste ranges? They are likely to cause problems
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
There's a lot of unecessary selecting of cells in your code, although can't see anything wrong with the line being highlighted by the debugger. Try the following:

Code:
Sub TEST()

Application.ScreenUpdating = False

With Sheets("Entry Sheet")
    .Unprotect
    Range(.Cells(7, 4), .Cells(7, 4)).End(xlToRight).Copy
End With
    
With Sheets("Log")
    .Unprotect
    .Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues
    .Range("I" & Rows.Count).End(xlUp).Offset(1, 0) = Application.UserName
    .Protect
End With

Sheets("Entry Sheet").Range(Cells(7, 4), Cells(7, 4)).End(xlToRight).ClearContents

Application.ScreenUpdating = True

End Sub
 

wilcowoods

Active Member
Joined
May 29, 2008
Messages
275
I haven't gone through your code thoroughly (can't see any "If constructions though to detect whether the cell is blank or not!) - BUT if it works how you want it to by ignoring the bug, haveyou tried inserting, "On Error Resume Next" at the beginning?

This is not good programming but it may work as a band-aid! For a more complete solution are you able to a section of the sheets you are copying and pasting to?
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,397
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS

ADVERTISEMENT

Try this:
Code:
    Dim wksEntry As Worksheet, wksLog As Worksheet
    Dim lngRow As Long
    Dim rngCopy As Range
    
    Set wksEntry = Sheets("Entry sheet")
    Set wksLog = Sheets("Log")
    
    With wksEntry
        .Unprotect
        ' get range to copy
        Set rngCopy = .Range("D7", .Range("D7").End(xlToRight))
    End With
    rngCopy.Copy
    With wksLog
        .Unprotect
        ' get next empty row in column B
        lngRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
        ' paste to B
        .Cells(lngRow, "B").PasteSpecial Paste:=xlPasteValues, _
                                        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        ' enter username in I on same row
        .Cells(lngRow, "I").Value = Application.UserName
        .Protect
    End With
    ' empty clipboard
    Application.CutCopyMode = False
    ' clear copied range
    rngCopy.ClearContents

    wksEntry.Protect
 

McDan1el

Board Regular
Joined
Sep 14, 2007
Messages
89
Try this:
Code:
    Dim wksEntry As Worksheet, wksLog As Worksheet
    Dim lngRow As Long
    Dim rngCopy As Range
    
    Set wksEntry = Sheets("Entry sheet")
    Set wksLog = Sheets("Log")
    
    With wksEntry
        .Unprotect
        ' get range to copy
        Set rngCopy = .Range("D7", .Range("D7").End(xlToRight))
    End With
    rngCopy.Copy
    With wksLog
        .Unprotect
        ' get next empty row in column B
        lngRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
        ' paste to B
        .Cells(lngRow, "B").PasteSpecial Paste:=xlPasteValues, _
                                        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        ' enter username in I on same row
        .Cells(lngRow, "I").Value = Application.UserName
        .Protect
    End With
    ' empty clipboard
    Application.CutCopyMode = False
    ' clear copied range
    rngCopy.ClearContents

    wksEntry.Protect
This appeared to work - but again I need to press END on the below and re-run then it works - as soon as i enter more data to "add" another record the same thing...

Run-time error "1004":

PasteSpecial method of Range class failed
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,397
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS

ADVERTISEMENT

What is the exact error message, and do you have any merged cells?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,397
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Good spot, Andrew.
Revised code:
Code:
    Dim wksEntry As Worksheet, wksLog As Worksheet
    Dim lngRow As Long
    Dim rngCopy As Range
    
    Set wksEntry = Sheets("Entry sheet")
    Set wksLog = Sheets("Log")
    
    With wksEntry
        .Unprotect
        ' get range to copy
        Set rngCopy = .Range("D7", .Range("D7").End(xlToRight))
    End With

    With wksLog
        .Unprotect
        rngCopy.Copy
        ' get next empty row in column B
        lngRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
        ' paste to B
        .Cells(lngRow, "B").PasteSpecial Paste:=xlPasteValues, _
                                        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        ' enter username in I on same row
        .Cells(lngRow, "I").Value = Application.UserName
        .Protect
    End With
    ' empty clipboard
    Application.CutCopyMode = False
    ' clear copied range
    rngCopy.ClearContents

    wksEntry.Protect
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,446
Messages
5,511,404
Members
408,846
Latest member
terraf

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top