Filtering

[milton]

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

 

[Alan Beban]

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

 

[steve]

Milton,

You can filter by a given territory, copy, and paste to the desired
worksheet. (usually when you copy a filtered region, only the visible cells
get copied - if not you can use the Edit > Go to > Special > Visible cells
to get there). Repeat for all catagories.

Do this while using the recorder and you will have the basic code.

Depending on how your catagories are defined or numbered, you can use
a loop. You'll have to edit the code to get that.

Suggest that you break the code into modules and call each module from a
master macro.
One macro to filter
One to copy and paste

I have a similar application that breaks the master into separate worksheets
(adding sheets to the workbook) and also makes separate workbooks for
the final product.

post back with your progress.