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("A1 0")

'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

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("A1 0")

'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