CODEWITHSUNDEEP
excelvba
user1996971
user1996971

Reputation: 543

Copying rows from one sheet to another

The following script seems like it should work, but I'm getting an "Object defined" error on the lines marked below. I can't find what's causing this at all...

Sub MailMerge()
Sheets.Add.Name = "MailMerge"
Dim MailMerge As Worksheet
Set MailMerge = Sheets("MailMerge")
Dim Rng As Range
Dim i, index, lastrow As Long
Dim Abstracts As Worksheet
Set Abstracts = Sheets("Abstracts")

lastrow = Abstracts.Cells(Rows.Count, 1).End(xlUp).row


For i = 1 To lastrow
    Set Rng = Abstracts.Range("O" & i)
        If WorksheetFunction.CountA(Rng) >= 1 Then
            Abstracts.Range("A" & i).Resize(0, 14).Copy _
            Destination:=MailMerge.Range("A" & i).Resize(0, 14)
            'this is where the error is occuring
         End If
Next

End Sub

Any suggestions?

Upvotes: 0

Views: 45

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Resize is not like OFFSET. It will set the size of the range to the size dictated. So you are setting the range size to 0 rows. It should be 1:

Sub MailMerge()
Sheets.Add.Name = "MailMerge"
Dim MailMerge As Worksheet
Set MailMerge = Sheets("MailMerge")
Dim Rng As Range
Dim i, index, lastrow As Long
Dim Abstracts As Worksheet
Set Abstracts = Sheets("Abstracts")

lastrow = Abstracts.Cells(Rows.Count, 1).End(xlUp).Row


For i = 1 To lastrow
    Set Rng = Abstracts.Range("O" & i)
        If WorksheetFunction.CountA(Rng) >= 1 Then
            Abstracts.Range("A" & i).Resize(1, 14).Copy _
            Destination:=MailMerge.Range("A" & i).Resize(1, 14)
            'this is where the error is occuring
         End If
Next

End Sub

Upvotes: 1

Related Questions