What is wrong with this code that generates an error?

Question

I don't know what is wrong with this code, could someone please spot the mistake?. Igives me the error:

object does not support this property or method.

Sub copyrow2()

Dim Lastro As Integer
Dim nLastro As Integer
Dim Rng As Range

nLastro = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row
Lastro = ActiveSheet.Cells(Rows.Count, 9).End(xlUp).Row + 1

If Lastro < nLastro Then

With oSht = ActiveSheet
Set Rng = oSht.Range("J" & Lastro & ":" & "k" & nLastro)
      Rng.Copy
      oSht.Range("H" & Lastro).Select
      ActiveSheet.Paste
End With

End If

You can put a break in the beginning of your code and step through until you get the error. — Gerhard Powell, Oct 22 '13 at 17:15
You've asked 9 questions, but not accepted any answers... why not? — David Zemens, Oct 22 '13 at 17:20
One may request an asker to accept answers (if the asker is new) but you may not force him to do it :) REF: http://meta.stackexchange.com/questions/119197/problem-with-users-not-accepting-answers — Siddharth Rout, Oct 22 '13 at 17:26

score 5 · Accepted Answer · edited May 23 '17 at 12:10

There are couple of problems with the code

Please use Option Explicit. That will force you to declare variables. For example oSht is undeclared.
When working with rows, never declare tham as Integers. There is a huge possibility that you may get an error in xl2007+. Declare them as Long
Avoid the use of ActiveSheet/Select etc. INTERESTING READ
Fully qualify Rows.Count. When working with several excel files in compatibility mode, it could result in an error if you don't fully qualify them.

Is this what you are trying?

Code: (untested)

Option Explicit

Sub copyrow2()
    Dim oSht As Worksheet
    Dim Lastro As Long, nLastro As Long
    Dim Rng As Range

    '~~> Change this to the relevant sheet
    Set oSht = ThisWorkbook.Sheets("Sheet1")

    With oSht
        nLastro = .Cells(.Rows.Count, 10).End(xlUp).Row
        Lastro = .Cells(.Rows.Count, 9).End(xlUp).Row + 1

        If Lastro < nLastro Then
            Set Rng = .Range("J" & Lastro & ":" & "k" & nLastro)
            Rng.Copy .Range("H" & Lastro)
        End If
    End With
End Sub

We should make a mandatory "Use Option Explicit" alert for all questions posted here... — enderland, Oct 22 '13 at 18:09

What is wrong with this code that generates an error?

1 Answers1