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