CODEWITHSUNDEEP
vbaexcel
Tanmoy
Tanmoy

Reputation: 815

Copying Range After Last Row

I am trying to copy the entire range from "Leads" sheet after the last row of "TempDataNew"

If Sheets("Leads").Range("A1") <> "" Then
                Set rngSource = Sheets("Leads").Range("A1").CurrentRegion
                lastrowdyn = rngSource.Rows.Count

                If lastrowdyn > 0 Then
                    Sheets("Leads").Range("A:A").Copy
                    Sheets("TempDataNew").Range ("A" & x)
                    Set rngSource = Sheets("TempDataNew").Range("A1").CurrentRegion
                    x = lastrowdyn + 1
                End If
            End If

I am getting the "Application Defined or Object Defined Error" when the code is trying to paste. Any thoughts?

Upvotes: 0

Views: 1707

Answers (4)

Coolshaikh
Coolshaikh

Reputation: 146

Why not just this?

Sub CopyRange()
    Dim wsLeads As Worksheet, wsTemp As Worksheet
    Dim lLastRowNew As Long, lRows As Long, iColumns As Integer

    Set wsLeads = Worksheets("Leads")
    Set wsTemp = Worksheets("TempDataNew")

    lLastRowNew = wsTemp.UsedRange.Rows(wsTemp.UsedRange.Rows.Count).Row
    lRows = wsLeads.UsedRange.Rows.Count
    iColumns = wsLeads.UsedRange.Columns.Count

    If wsLeads.Range("A1").Value <> "" And lRows > 0 Then
        wsTemp.Cells(lLastRowNew + 1, 1).Resize(lRows, iColumns).Value = wsLeads.UsedRange.Value
    End If
End Sub

Upvotes: 0

Matt Cremeens
Matt Cremeens

Reputation: 5151

From what I can tell, you want to copy to the bottom of the used range of TempDataNew, not Leads. So change this

Set rngSource = Sheets("Leads").Range("A1").CurrentRegion

to this

Set rngSourceTempDatNew = Sheets("TempDatNew").Range("A1").CurrentRegion
lastrowdynTempDatNew = rngSourceTempDatNew.Rows.Count

Also, I'm guessing you really don't want or need to copy the entire column A of Leads. So this needs to be changed. 

Sheets("Leads").Range("A:A").Copy

or that is exactly what you'll end up doing. May I recommend also finding the last row of the used range in column A of Leads as you want to do for TempDatNew. Maybe something like

Set rngSourceLeads = Sheets("Leads").Range("A1").CurrentRegion

Now just copying from the range in Leads to the right spot in TempDatNew can be done with something like this

rngSourceLeads.Copy _ 
destination:=Worksheets("TempDatNew").Range("A" & lastrowdynTempDatNew + 1)

I can't exactly tell why you are storing the last row plus one to x, but my current thinking is that it isn't needed for your purposes. Every time this code is ran, it will update the last row of TempDatNew for you and you just simply paste in the row after that.

So, all together you have something more succinct and accurate with

If Sheets("Leads").Range("A1") <> "" Then
    Set rngSourceTempDatNew = Sheets("TempDatNew").Range("A1").CurrentRegion
    lastrowdynTempDatNew = rngSourceTempDatNew.Rows.Count
    Set rngSourceLeads = Sheets("Leads").Range("A1").CurrentRegion
    rngSourceLeads.Copy destination:=Worksheets("TempDatNew").Range("A" & lastrowdynTempDatNew + 1)
End If

EDIT If you only want to copy column A in "Leads" change this

Set rngSourceLeads = Sheets("Leads").Range("A1").CurrentRegion

to this

lastrowdynLeads = Sheets("Leads").Cells(65000, 1).End(xlup).Row
Set rngSourceLeads = Sheets("Leads").Range("A1:A" & lastrowdynLeads)

This assumes you have no data below row 65000 in column A in sheet "Leads".

Upvotes: 1

Tyeler
Tyeler

Reputation: 1118

I think I found the problem you were originally experiencing in your code... Your first IF statement says If Sheets("Leads").Range("A1") <> "" Then. Your referencing a range object, and comparing it to a value. Your error should go away if you use Sheets("Leads").Range("A1").Value <> "".

I'm not sure why you want to use .CurrentRegion if you're only working with a single column (I'm also not the most versed in VBA logic) but if you're just trying to find the last row, you could use something like this:

Dim Leads, TempDataNew as Worksheet
Set Leads = ThisWorkbook.Sheets("Leads")
Set TempDataNew = ThisWorkbook.Sheets("TempDataNew")

    lastrowdyn = Leads.Cells(Leads.Rows.Count, "A").End(xlUp).Row

    If Leads.Range("A1").Value <> "" And lastrowdyn > 0 Then
        Leads.Range("A:A").Copy Destination:=TempDataNew.Range("A" & x)
        x = lastrowdyn + 1
    End If

Upvotes: 0

rohrl77
rohrl77

Reputation: 3337

You have not specified what x is before you begin running the code. I have added a line to give x a value. The way you had it, excel was evaluating x to be 0, thus resulting in a range called Range("A0")... that doesn't exist.

If Sheets("Leads").Range("A1") <> "" Then
    Set rngSource = Sheets("Leads").Range("A1").CurrentRegion
    lastrowdyn = rngSource.Rows.Count
    x = 1 'Whatever integer it is supposed to start at
    If lastrowdyn > 0 Then
        Sheets("Leads").Range("A:A").Copy
        Sheets("TempDataNew").Range ("A" & x)
        Set rngSource = Sheets("TempDataNew").Range("A1").CurrentRegion
        x = lastrowdyn + 1
    End If
End If

Upvotes: 0

Related Questions