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]

 

[Andrew Poulsom]

On which line do you get the error? By the way, in the closing the code tag you need a forward slash (/) not a backslash (\).

 

[liferg]

Sorry, it's been a while since I have been on here. I will repost the code and highlight where the error is.

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

 

[Andrew Poulsom]

The name must be invalid. What is assigned to UListValue when the error occurs?

 

[liferg]

It appears to be erroring out on the blanks, but I tried to remove all of the blanks before this part of the macro runs and for some reason they are still there. In column B there are some #N/A's and I have included a filter in order to remove these, but for some reason or another, they do not go away so it's causing this part of the macro to wig out. Are you able to look at the filtering and deletion part of the macro before the UListValue and tell me what I need to do to correct this part?

 

[Andrew Poulsom]

You seem to be filtering column B for #N/A but using column A in your Collection.

 

[liferg]

That's correct. The #N/A's I have manually pulled to it's own page within the macro and once it has copy and pasted the information, I would like to delete it so that the UListValue can run without any interruptions. Does that make sense?

 

[Andrew Poulsom]

But isn't it column A that has the invalid name?

 

[liferg]

Yes. Okay here's the layout in column A there are some items that have funny characters and some blanks, however I still need some of that information. The ones that have blanks are going to be the ones that aren't assigned to anyone and I need to pull that information, so I filtered it by column B which contains the #N/A's and tried to pull it in it's own tab, but it's not doing that nor is it deleting them. I figured if I could delete them, then the UListValue could run properly for the ones that have names in them in column A. Once I have removed the #N/A's in column B, it will remove the blanks, and leave me with valid names.

 

[Andrew Poulsom]

What's the purpose of this?

MySheet.Select
Range("A2").Select
Selection.Delete Shift:=xlUp