Application-defined or object-defined error when using copy function VBA

Question

I am trying to copy and paste all the data from one worksheet to a separate one in a completely different workbook. The code runs properly until the actual line trying to copy and paste the data: the error I get when running this line is:

"Run-time error '1004': Application or user-defined error."

Not sure if I even need the With statement or not...any help would be appreciated! (Also I changed my file names just for privacy but they are complete in my actual code!)

Option Explicit

Sub btnCopyingData_Click()

' Copying data from one workbook to another
Dim fileDest As String, fileSource As String
Dim wbDest As Workbook, wbSource As Workbook
Dim wsDest As Worksheet, wsSource As Worksheet
Dim lrDest As Long, lrSource As Long

'The FILE that data is being copied TO
fileDest = "C:\Users\rest of path...Tar SAPCL Copy.xlsx"

    'The WORKBOOK that data is being copied TO
    Workbooks.Open Filename:=fileDest
    Set wbDest = Workbooks("Tar SAPCL Copy.xlsx")

        'The WORKSHEET that data is being copied TO
        Set wsDest = wbDest.Worksheets(1)

            'The ROW to which data will be pasted in the destination
            lrDest = wsDest.Range("A" & wsDest.Rows.Count).End(xlUp).Row + 1
'---------------------------------------------------'
'The FILE that data is being copied FROM
fileSource = "C:\Users\Rest of path...SAPCL_20180720 Part 1.xlsx"

    'The WORKBOOK that data is being copied FROM
    Workbooks.Open Filename:=fileSource
    Set wbSource = Workbooks("SAPCL_20180720 Part 1.xlsx")

        'The WORKSHEET that data is being copied FROM
        Set wsSource = wbSource.Worksheets(1)

            'The LAST ROW of the data being copied
            lrSource = wsSource.Range("A" & wsSource.Rows.Count)


With wsSource
    wsSource.Range("A1:V" & lrSource).Copy Destination:=wsDest.Range("A1" & 
    lrDest)
End With

End Sub

The error is here:

With wsSource
    wsSource.Range("A1:V" & lrSource).Copy Destination:=wsDest.Range("A1" & lrDest)
End With

@Davesexcel I am pasting my data one row below the current data which is why I have that last row variable lrDest! — Analia Quirk
– Analia Quirk, Commented Jul 23, 2018 at 15:41

Vityata · Accepted Answer · 2018-07-23 15:48:21Z

4

In your code you are getting the value written on the last row of column A, which is usually an empty cell:

lrSource = wsSource.Range("A" & wsSource.Rows.Count)

Change it to the following:

lrSource = wsSource.Range("A" & Rows.Count).End(xlUp).Row

Some ideas how to get the last row:

Then change this:

With wsSource
    wsSource.Range("A1:V" & lrSource).Copy Destination:=wsDest.Range("A1" & 
    lrDest)
End With

to this:

With wsSource
    .Range("A1:V" & lrSource).Copy Destination:=wsDest.Range("A1")
End With

Or depending on what exactly do you need, this could be ok as well:

With wsSource
   .Range("A1:V" & lrSource).Copy Destination:=wsDest.Range("A" & lrDest)
End With

edited Jul 23, 2018 at 15:48

answered Jul 23, 2018 at 15:34

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Analia Quirk Over a year ago

The same error still pops up on that line even with that change!

Vityata Over a year ago

@AnaliaQuirk - try again.

Analia Quirk Over a year ago

Changing the first part of the range to just "A" or "A1" doesn't work either...trying to think if I defined something improperly above which is why this line is failing

Vityata Over a year ago

Can you replace lrSource with 5 and see what is happening?

Bugs · Accepted Answer · 2018-08-03 09:20:02Z

0

My lrSource variable line wasn't complete so it was being recorded as a value of 0 instead of the actual row integer value. I fixed this line and the code now runs.

edited Aug 3, 2018 at 9:20

Bugs

4,4799 gold badges34 silver badges41 bronze badges

answered Jul 23, 2018 at 15:52

Analia Quirk

556 bronze badges

Collectives™ on Stack Overflow

Application-defined or object-defined error when using copy function VBA

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related