CODEWITHSUNDEEP
vbaexcelcopy-paste
Analia Quirk
Analia Quirk

Reputation: 55

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

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

Upvotes: 3

Views: 1423

Answers (2)

Analia Quirk
Analia Quirk

Reputation: 55

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.

Upvotes: 0

Vityata
Vityata

Reputation: 43565

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

Upvotes: 4

Related Questions