Object required error in VBA while declaring custome sheet name

1
votes

Hi I have the below code to move data. This works fine when the default sheet names are used. However when I use a different name for the sheets (instead of sheet1, sheet2) I get "Object required" error.

Here "Latency" is sheet1 and "TP" is sheet2.

Can someone guide me on how to fix it?

Sub sbMoveData()
Dim lRow As Integer, i As Integer, j As Integer
'Find last roe in Sheet1
lRow = Latency.Cells.SpecialCells(xlLastCell).Row
j = 1
For i = 1 To lRow
If UCase(Latency.Range("E" & i)) = "COMPATIBLE" And UCase(Latency.Range("O" & i)) = "Pass" Then
Sheets("Latency").Range("M" & i).Copy Destination:=Sheets("TP").Range("A" & j)
j = j + 1
End If
Next

End Sub

3
vbaexcel
Latency is clearly a worksheet, but has not been Dimmed or Set in the scope of this sub. – Gary's Student
I'll try to say what @Gary's Student wrote above, where in this Sub do you have Dim Latency As Worksheet , and later Set Latency = Worksheets("Yoursheetname") ? While later you are using it correctly with Sheets("Latency") – Shai Rado

3 Answers

1
votes

you did it right at the line

Worksheets("Latency").Range("M" & i).Copy Destination:=Worksheets("TP").Range("A" & j)

but before, you used Latency as if it was an object, while it is just a name. To obtain a sheet object by its name, always use sheets("name"), or Worksheets("name")

Sub sbMoveData()
    Dim lRow As Integer, i As Integer, j As Integer
    'Find last roe in Sheet1
    With Worksheets("Latency")
        lRow = .Cells.SpecialCells(xlLastCell).Row
        j = 1
        For i = 1 To lRow
            If UCase(.Range("E" & i)) = "COMPATIBLE" And UCase(.Range("O" & i)) = "Pass" Then
                .Range("M" & i).Copy Destination:=Worksheets("TP").Range("A" & j)
                j = j + 1
            End If
        Next
    End With
End Sub
4
votes

When you do Sheet1.Range, you're referencing a global-scope Worksheet object that VBA automatically declares for you. That "free" object points to ThisWorkbook.Worksheets("Sheet1") by default, and using it instead of fetching the exact same object reference from the Worksheets collection is a very good idea.

Now, if you labelled that Sheet1 worksheet "Latency", you've set its Name, but not its CodeName - and it's the CodeName that VBA uses to create the global identifier you can use in code. You can change that in the Properties toolwindow (F4):

Properties

Notice the (Name) (first) property still says "Sheet1"? Change that to Latency and now this code becomes valid:

lRow = Latency.Cells.SpecialCells(xlLastCell).Row

Properties

Now, once you start using the Latency global identifier, use it everywhere - don't go and start fetching the exact same worksheet reference off the ActiveWorkbook.Worksheets or ActiveWorkbook.Sheets collections!

Instead of this:

Sheets("Latency").Range("M" & i).Copy

You do that:

Latency.Range("M" & i).Copy

Do the same for the other sheet, too. Note that the Sheets collection can contain charts, not just worksheets - when you do fetch a worksheet object reference from a collection (say, from a worksheet you've created at runtime), you should use the Worksheets collection to do so.

You should also look into properly indenting (disclaimer: this link points to my pet project's website) your code, too.

0
votes

try this:

If UCase(Latency.Range("E" & i)) = "COMPATIBLE" And UCase(Latency.Range("O" & i)) = "Pass" Then
Worksheets("Latency").Range("M" & i).Copy Destination:=Worksheets("TP").Range("A" & j)
j = j + 1
End If