VBA Macro was working, now getting a 1004 error

[bjohnson2235]

Hello,

I have a macro that worked fine for a month and a half, but is now giving me a 1004 Runtime error "Application-defined or object defined error". Below is the code in which the macro is errors out on.

The gist of the macro is to filter by a date provided by the user, then filter by a fixed column on that date. Where the error then occurs is when the macro goes to a column "T", and tries to find the first visible row. The code is in a form, and works well on one option of the form, but not this option.

'Gets the date
    Dim Start_date As Integer
    Dim UserEntry As String
    Dim Msg As String
    Dim TheDate As String
    Msg = "Enter Wire Journal date for processing (MM/DD/YYYY)"
    
        Do
            UserEntry = InputBox(Msg)
         If UserEntry = "" Then Exit Sub
         If IsDate(UserEntry) Then
            ActiveSheet.Range("AI1").Value = Format(UserEntry, "mm/dd/yyyy")
        Else
            Msg = "Please try again.  Enter date as mm/dd/yyyy"
      End If
      Exit Do
      Loop
      
    'Filters by the date entered in AI1, filters by deposit date
    ActiveSheet.Range("$A$1:$AA$1").End(xlDown).AutoFilter Field:=26, Criteria1:= _
    "=" & Range("AI1"), Operator:=xlAnd
    
    'Filters by Receipt Applied
    Sheets("Wires").Select
    Dim A As Range, Z As Integer
    Set A = Range(Range("$A$1:$AA$1"), Range("$A$1:$AA$1").End(xlDown))
    A.AutoFilter Field:=13, Criteria1:="Receipt Applied"
    Z = WorksheetFunction.Count(A.Cells.SpecialCells(xlCellTypeVisible))
    If Z = 0 Then
    MsgBox "There are no items with a status of Receipt Applied. Update the Wires tab and re-run the Macro"
    
    Else
      
    'Finds the first visible row in Column T for Receivable ID
    
    Sheets("Wires").Select
    With Worksheets("Wires").AutoFilter.Range
       [COLOR=rgb(184, 49, 47)] Range("T" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select    ' This is the row that causes the 1004 error[/COLOR]
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy

 

[RayFrye]

Have you used the VBA Debug option to step into and thru the code, one line at a time, to determine which line of code throws the error.

 

[MARK858]

Have you used the VBA Debug option to step into and thru the code, one line at a time, to determine which line of code throws the error.

@RayFrye It is the middle line below that @bjohnson2235 has tried to manually format in the code (and put a comment at the end )

    With Worksheets("Wires").AutoFilter.Range
       [COLOR=rgb(184, 49, 47)] Range("T" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select    ' This is the row that causes the 1004 error[/COLOR]
    End With

@bjohnson2235 if you want to manually format code when posting you need to use the RICH tag option rather than the VBA tag option.

 

[Michael M]

Don't know how it worked previously...
It is missing an End IF statement after the End With Statement !

 

[bjohnson2235]

The error is highlighted below in red (correctly tagged, thanks Mark858). The end if statement is much lower in the code, but the macro errors out prior to getting to the end if statement. I did not add all of the code since it is much longer. I only originally added the code where it starts to where the macro errors out with the 1004 error.

I believe the error is due to the offset, because if I remove the offset, then the error goes away. The issue is that I need the offset to copy the correct information.

    With Worksheets("Wires").AutoFilter.Range
       Range("T" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy

 

[Fluff]

Try replacing all that code in post#5 with

    Worksheets("Wires").AutoFilter.Range.Offset(1).Columns("T").Copy

 

[Akuini]

    Z = WorksheetFunction.Count(A.Cells.SpecialCells(xlCellTypeVisible))
    If Z = 0 Then

I think Z is never 0, because at least the header always visible, so try:

    Z = WorksheetFunction.Count(A.Cells.SpecialCells(xlCellTypeVisible))
    If Z = 1 Then