CODEWITHSUNDEEP
vbaexcel
user882670
user882670

Reputation:

Copy Paste Range in Excel VBA error

I have a table with 5 columns (B to F) and a variable number of filled rows. I want to copy the last 3 filled cells to a fixed range on the same column starting on row 101.

This the code I'm using:

Dim WSPL As Worksheet
 For i = 2 To 6
        For j = 7 To 1 Step -1
            If Not IsEmpty(WSPL.Cells(j, i).Value) Then
                WSPL.Range(Cells(j - 2, i), Cells(j, i)).Copy Destination:=WSPL.Cells(101, i)
                Exit For
            End If
        Next j
    Next i

This is returning error:

Run-time error: 1004
Method 'Range' of object'_Worksheet' failed

On line 5 of my code above. What is wrong with this code?

Upvotes: 1

Views: 2016

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

The problem is this line

WSPL.Range(Cells(j - 2, i), Cells(j, i)).Copy Destination:=WSPL.Cells(101, i)

Your cells object is not fully qualified

Try this

With WSPL
    .Range(.Cells(j - 2, i), .Cells(j, i)).Copy Destination:=.Cells(101, i)
End With

Notice the DOTs before Cells?

Upvotes: 2

Related Questions