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 |
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 |
All times are GMT +1. The time now is 05:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com