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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

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,772
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
38,772
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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
38,772
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,772
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,982
Members
430,100
Latest member
namhnz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top