Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering multiple worksheets
I have 3 worksheets which have duplicate information on all the thre
sheets. Is there a way to filter across all the sheets at once? Thanks all -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering multiple worksheets
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering multiple worksheets
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering multiple worksheets
For starters record a macro as you set up the filters on one sheet.
Got to the Visual Basic editor and copy the recorded code and past into a message for this newsgroup. and we have a look at it and advic on a way to clean up & change the code to include filter on all sheet -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering multiple worksheets
Here is the code for the recorded macro for just a simple filtering.
Currently it is filtered for 2 out of 18 collumns. At different time i would need to filter for more than 2 columns with multiple condition in a single colums eg. for companies A, B, C and D. Sub MacroFilter1() ' ' MacroFilter1 Macro ' Macro recorded 6/29/2004 by Administrator ' ' Selection.AutoFilter Field:=3, Criteria1:="sfg" ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Selection.AutoFilter Field:=2, Criteria1:="sales" End Sub I am not good with using excel and do not know VB so thank you for an help you can give -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering multiple worksheets
ryssa
copy and paste all of the code starting with Option Explicit AND paste into your module sheet Filter one sheet however you want then Press Alt + f8 select MacroFilter1 this will run the macro setting the filters on the other sheets to th same as on the active sheet Test using different filter combinations If ok then we can look at other methods of activating the macro Option Explicit Dim sCriteria() As String Sub MacroFilter1() Dim wS As Worksheet Dim wsAct As Worksheet Dim iCol As Integer Set wsAct = ActiveSheet For Each wS In Worksheets Debug.Print wS.Name If wsAct.Name < wS.Name Then For iCol = 1 To _ Cells(1, Columns.Count).End(xlToLeft).Column _ Step 1 Call FilterCriteria(wsAct.Cells(1, iCol)) Select Case sCriteria(0) Case "" wS.Rows(1).AutoFilter Field:=1 Case 0 wS.Rows(1).AutoFilter Field:=iCol, _ Criteria1:=sCriteria(1) Case Else wS.Rows(1).AutoFilter Field:=iCol, _ Criteria1:=sCriteria(1), _ Operator:=sCriteria(0), _ Criteria2:=sCriteria(2) End Select Next iCol End If Next wS End Sub Sub FilterCriteria(oRng As Range) 'Dim sFilter As String ReDim sCriteria(2) On Error GoTo NoMoreCriteria With oRng.Parent.AutoFilter 'Is it in the AutoFilter range? If Intersect(oRng, .Range) Is Nothing Then _ GoTo NoMoreCriteria 'Get the filter object for the appropriate column With .Filters(oRng.Column - .Range.Column + 1) 'Does this column have an AutoFilter criteria? If Not .On Then GoTo NoMoreCriteria 'It has one! sCriteria(1) = .Criteria1 'Does it have another (i.e. the "Custom" filter)? sCriteria(0) = .Operator sCriteria(2) = .Criteria2 End With End With NoMoreCriteria: End Su -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering multiple worksheets
Tom Ogilvy posted some code for this in the following thread:
http://groups.google.com/groups?&thr...upernews.co m ryssa < wrote: Here is the code for the recorded macro for just a simple filtering. Currently it is filtered for 2 out of 18 collumns. At different times i would need to filter for more than 2 columns with multiple conditions in a single colums eg. for companies A, B, C and D. Sub MacroFilter1() Selection.AutoFilter Field:=3, Criteria1:="sfg" Selection.AutoFilter Field:=2, Criteria1:="sales" End Sub I am not good with using excel and do not know VB so thank you for any help you can give. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering multiple worksheets
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple lookups and filtering | Excel Discussion (Misc queries) | |||
Filtering grouped worksheets | Excel Discussion (Misc queries) | |||
Filtering Multiple Columns... | Excel Discussion (Misc queries) | |||
Still need help: filtering data bewteen worksheets | Excel Worksheet Functions | |||
joining to worksheets or filtering not sure of correct terminology | New Users to Excel |