Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Question
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 Vick wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro question | Excel Discussion (Misc queries) | |||
macro question | Excel Discussion (Misc queries) | |||
Macro Question | Excel Worksheet Functions | |||
Macro Question | Excel Worksheet Functions | |||
Macro Question | Excel Worksheet Functions |