Run time error 1004: "No list was found.."

I ran this code

Please Login or Register  to view this content.

But there is a Run time error 1004: "No list was found. Select a single cell within your list, and then click the command again."

It refers to this part , (second "paragraph" specifically)

Please Login or Register  to view this content.

Although I criteria is set, it basically says ( I believe) that a cell is not selected.

Any ideas?

The specific error "no list was found..." indicates that the Autofilter is
not recognizing the range you want filtered. Autofilter requires that the
range to filter be in the standard "list" format and that is apparently what
the code is not recognizing. List format means no blank rows in the range,
no blank columns, and each column has a header that is formatted somehow
distinctly (e.g. bold text) from the rest of the range. Something in this is
missing for your .Autofilter to be giving the error you received.
--
- K Dales


"hachiroku" wrote:

>
> I ran this code
>
> Code:
> --------------------
> Sub Macro2()
> Dim wbTemplate As Workbook, CopyRng As Range
> Dim iLtr As Integer, Criteria As String, FilePath As String
>
> 'can save this as a string so you can easily reference it in the code
> FilePath = "S:\Wkgrps\Cbbfs\Product Development - Deposit\Large Amount Report\By ARM\new\"
>
> On Error Resume Next
> 'makes sure the workbook is open
> 'if this line of code causes an error (workbook is not open)
> Set wbTemplate = Workbooks("Large Amount Report By individual ARM Templete.xls")
> If Err <> 0 Then
> 'open the workbook and set to variable
> Set wbTemplate = Workbooks.Open("Insert Path To File Here\Large Amount Report By individual ARM Templete.xls")
> End If
> On Error GoTo 0
>
>
> With ThisWorkbook.Sheets("Sheet1")
> '65~80 refers to letters A~P
> For iLtr = 65 To 80
> 'create the string to filter for. For example, if iLtr=73 then the filter criteria will be "FBI"
> Criteria = "FB" & Chr(iLtr)
>
> 'filter for the string
> .Cells.AutoFilter Field:=1, Criteria1:=Criteria
>
> 'makes sure there are visible cells in the range A10:F50 after filtering
> 'if there are no visible cells within the range, error occurs and CopyRng is not set
> On Error Resume Next
> Set CopyRng = .Range("A10:F50").SpecialCells(xlCellTypeVisible)
>
> 'if error was NOT detected on above line of code
> 'and CopyRng variable was assigned a range
> If Not CopyRng = Nothing Then
> On Error GoTo 0
> 'copy the range to the template workbook
> CopyRng.Copy Destination:=wbTemplate.Sheets("Sheet1").Range("A10")
>
> 'save the template workbook using criteria as part of filename
> wbTemplate.SaveAs Filename:= _
> FilePath & "Large Amount Report " & Criteria & ".xls", FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> End If
>
> 'reset the copyrng to be blank
> Set CopyRng = Nothing
> On Error GoTo 0
> Next iLtr
> End With
>
> End Sub
> --------------------
>
>
> But there is a Run time error 1004: "No list was found. Select a single
> cell within your list, and then click the command again."
>
> It refers to this part , (second "paragraph" specifically)
>
> Code:
> --------------------
> With ThisWorkbook.Sheets("Sheet1")
> '65~80 refers to letters A~P
> For iLtr = 65 To 80
> 'create the string to filter for. For example, if iLtr=73 then the filter criteria will be "FBI"
> Criteria = "FB" & Chr(iLtr)
>
> 'filter for the string
> .Cells.AutoFilter Field:=1, Criteria1:=Criteria
> --------------------
>
>
> Although I criteria is set, it basically says ( I believe) that a cell
> is not selected.
>
> Any ideas?
>
>
> --
> hachiroku
> ------------------------------------------------------------------------
> hachiroku's Profile: http://www.excelforum.com/member.php...o&userid=34039
> View this thread: http://www.excelforum.com/showthread...hreadid=544225
>
>