Macro Question

[Guest]

I have a list of cells that contain client #'s in one sheet and another sheet
that has a bunch of data which I need to filter for each client #. (After it
is filtered, I'm copying that data into a new sheet, that part I have figured
out) What code can I use to look at a range of cells, and for every non-blank
cell, switch to the data sheet and select the filter, and then repeat for
every non blank cell one time?

Thanks
Vick

 

[Dave Peterson]

This may give you an idea:

Option Explicit
Sub testme()

Dim ListRng As Range
Dim myCell As Range
Dim myFilterRng As Range
Dim LastRow As Long
Dim LastCol As Long
Dim ColToFilter As Long

With Worksheets("Sheet2")
Set ListRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Worksheets("Sheet1")
ColToFilter = .Range("a1").Column
.AutoFilterMode = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set myFilterRng = .Range("a1", .Cells(LastRow, LastCol))

For Each myCell In ListRng.Cells
If IsEmpty(myCell.Value) Then
'skip it
Else
.AutoFilterMode = False
myFilterRng.AutoFilter field:=ColToFilter, _
Criteria1:=myCell.Value
'do the copy
End If
Next myCell

.AutoFilterMode = False
End With

End Sub

=====
If you're creating separate worksheets for each of the client numbers, you may
want to look at these:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb