Run-time error '424': Object Required

[j_williams]

I am receiving the error message
"Run-time error '424': Object required"

Can someone help me fix this, code below:

Sub UpcomingPMsbyCell()

Dim i As Long
Dim lngEndRowInv As Long

With Excel.Application.ActiveSheet
    i = 2
    lngEndRowInv = .Range("A" & .Rows.Count).End(xlUp).row

        While i <= lngEndRowIn
               If WorksheetFunction.CountIf(.Columns(5), "Rotatives") <> 0 Then
                      .AutoFilterMode = False
                      .Range("A1:H1").AutoFilter
                      .Range("A1:H1").AutoFilter Field:=5, Criteria1:="Rotatives"
                      .Range("A1:H1").AutoFilter Field:=7, Criteria1:="Active"
                      .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
                            Destination:=Dashboard.Range("A1")
                      .AutoFilterMode = False
                      Application.CutCopyMode = False
              End If
               i = i + 1
        Wend
End With
End Sub

[Leith Ross]

Hello j_williams,

Try this...

Sub UpcomingPMsbyCell()


    Dim i As Long
    Dim lngEndRowInv As Long


        With Excel.Application.ActiveSheet
            i = 2
            lngEndRowInv = .Range("A" & .Rows.Count).End(xlUp).Row
    
            While i <= lngEndRowInv
                If WorksheetFunction.CountIf(.Columns(5), "Rotatives") <> 0 Then
                    .AutoFilterMode = False
                    .Range("A1:H1").AutoFilter
                    .Range("A1:H1").AutoFilter Field:=5, Criteria1:="Rotatives"
                    .Range("A1:H1").AutoFilter Field:=7, Criteria1:="Active"
                    .UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=Dashboard.Range("A1")
                    .AutoFilterMode = False
                    Application.CutCopyMode = False
                End If
                i = i + 1
            Wend
        End With


End Sub

[SamT]

Destination:=Dashboard.Range("A1")

Is Dashboard a WorkSheet's Tab Name, A Worksheet's CodeName, or an Object's Name? Frim the Error, I suspect it is a WorkSheet's Tab Name. Try

Destination:=Sheets("Dashboard").Range("A1")

[j_williams]

Ahhh. Yes it's a worksheets name, this fixed it. Thank you!