If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, something
like the following, which assumes your report data is on the Sheet with
Index Number 4, in a range called "reportRange", should work (watch for
wordwrap):

Sub test1000()
Dim rng As Range, rngOut As Range, arr
Dim i As Integer
Set rng = Sheets(4).Range("reportRange")
arr = rng
On Error Resume Next
For i = 1 To 5
Sheets(Sheets(4).Index + i).Cells.ClearContents
arr1 = ArrayRowFilter1(arr, UBound(arr, 2), i)
Set rngOut = Sheets(Sheets(4).Index +
i).Range("A1").Resize(UBound(arr1), UBound(arr, 2))
If Err = 0 Then
rngOut.Value = arr1
Else
Set rngOut = Sheets(Sheets(4).Index +
i).Range("A1").Resize(, UBound(arr, 2))
rngOut.Value = "No records meet the condition."
End If
Err = 0
Next
End Sub

Alan Beban

milton wrote:
Hi can anyone help me with some code for an excel spreadsheet?
Right now I run some crystal reports for different territories(1-5). I am
wondering if there is a way to have a macro filter the last colum which
contains each of the territories 1-5. Basically after I run individual
reports, the last colum contains the territory #, for each spreadsheet. I
also have a master file that will contain all of the territory #'s in the
last colum. Maybe it would be easier to use that file to manipulate the auto
filter. I would like to know if the spreadsheet could contain a worksheet
tab for each of the territories via a filter of some sort. I have to run
seperate reports and export them into excel and run a custom macro for each
report. It would be easier if I ran one report and ran a macro which
filtered each territory into seperate worksheets within the same file( if at
all possible).

Thanks in advance.

Milton