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

Filtering multiple worksheets
 

Not to my knowledge unless you use a macr

--
Message posted from http://www.ExcelForum.com

Filtering multiple worksheets
 

How would I do that

--
Message posted from http://www.ExcelForum.com

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

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

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

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

Filtering multiple worksheets
 

Thank you very much Debra.

ryss

--
Message posted from http://www.ExcelForum.com