Run-time error '1004', attempting to pull names from column A into individual worksheets

[liferg]

I am receiving the following error message when trying to pull names from column A into individual worksheets, Run-time error '1004': Application-defined or obeject-dfined error.

Here is part of the code

:Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$Y$3953").AutoFilter Field:=2, Criteria1:="#N/A"
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet12").Select
    ActiveSheet.Paste
    Range("A1").Select
    Columns("A:A").EntireColumn.AutoFit
    Range("A1").Select
    Sheets("Sheet12").Name = "UNASSIGNED VENDORS"
    MySheet.Select
    Range("A2").Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$Y$5000").AutoFilter Field:=1, Criteria1:="=*HSBC*" _
        , Operator:=xlAnd
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter
    Range("A1").Select
    Selection.AutoFilter

If MySheet.AutoFilterMode = False Then
    Exit Sub
End If
Set MyRange = Range(MySheet.AutoFilter.Range.Columns(1).Address)
Set UList = New Collection
On Error Resume Next
For i = 2 To MyRange.Rows.Count
UList.Add MyRange.Cells(i, 1), CStr(MyRange.Cells(i, 1))
Next i
On Error GoTo 0
For Each UListValue In UList
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets(CStr(UListValue)).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    MyRange.AutoFilter Field:=1, Criteria1:=UListValue
    MySheet.AutoFilter.Range.Copy
    Worksheets.Add.Paste
    ActiveSheet.Name = Left(UListValue, 30)
    Cells.EntireColumn.AutoFit
Next UListValue
MySheet.AutoFilter.ShowAllData
MySheet.Select
End Sub[\code]

 

[liferg]

Let's say I have selected a few items to be moved to a different sheet like the #N/A's and I copy and paste them, then when I go back to MySheet, then I want to delete that selection. Should I be using a different code for that?

 

[Andrew Poulsom]

That only deletes row 2. Try:

MySheet.Select
With MySheet.AutoFilter.Range
    .Offset(1).Resize(.Rows.Count -1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

 

[liferg]

Ok, that seemed to work, now I have another error: Run-time error '9': Subscript out of range.
It looks like it didn't do the UListValue, so now it's causing it to error out on the next macro.

    MySheet.Select
    With MySheet.AutoFilter.Range
    .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$Y$5000").AutoFilter Field:=1, Criteria1:="=*HSBC*" _
        , Operator:=xlAnd
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter
    Range("A1").Select
    Selection.AutoFilter

If MySheet.AutoFilterMode = False Then
    Exit Sub
End If
Set MyRange = Range(MySheet.AutoFilter.Range.Columns(1).Address)
Set UList = New Collection
On Error Resume Next
For i = 2 To MyRange.Rows.Count
UList.Add MyRange.Cells(i, 1), CStr(MyRange.Cells(i, 1))
Next i
On Error GoTo 0
For Each UListValue In UList
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets(CStr(UListValue)).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    MyRange.AutoFilter Field:=1, Criteria1:=UListValue
    MySheet.AutoFilter.Range.Copy
    Worksheets.Add.Paste
    ActiveSheet.Name = Left(UListValue, 30)
    Cells.EntireColumn.AutoFit
Next UListValue
MySheet.AutoFilter.ShowAllData
MySheet.Select
End Sub

Sub RNICLEANUP2()
' Keyboard Shortcut: Ctrl+w

    MyName = "Weekly RNI"
    DirPath = "C:\Documents and Settings\lorrie.hoyle\Desktop\RNI\2013"
    DateStr = Format(Date, "mm-dd-yy")
Sheets("C JACOBI").Select
    ActiveWorkbook.Worksheets("C JACOBI").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("C JACOBI").Sort.SortFields.Add Key:=Range( _
        "P2:P5000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("C JACOBI").Sort
        .SetRange Range("A1:Y5000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(14), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Columns("N:N").Select
    Selection.Style = "Comma"
    Range("A1").Select
Sheets("G ANDERSON").Select
    ActiveWorkbook.Worksheets("G ANDERSON").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("G ANDERSON").Sort.SortFields.Add Key:=Range( _
        "P2:P5000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("G ANDERSON").Sort
        .SetRange Range("A1:Y5000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Andrew Poulsom]

How can you be sure that that worksheet exists? And what is the purpose of this?

    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp

 

[liferg]

I figured it out. The autofilter was turned off. It's working properly now. The worksheet will exist if the UListValue runs, but the UListValue will not run if the filter is not there. Thanks for your help.