PDA

View Full Version : filtering data from two or more sheets



naris
08-28-2008, 07:28 PM
I have a macro to filtering data from two or more sheets in the result sheet, I hope this macro will useful for you.
And I hope someone want revised this macro become simplest macro script.

Thanks you.
Naris

mdmackillop
08-29-2008, 01:45 AM
Hi Naris,
Make use of Variables to store ranges. It makes the code more flexible.


Option Explicit
Sub Filter2()
Dim wsRes As Worksheet
Dim i As Long
Dim FRange As Range
Dim CrRange As Range
Dim Tgt As Range
Set wsRes = Sheets("Result")
Set CrRange = wsRes.Range("B1:B2")
For i = 2 To Sheets.Count
Set Tgt = wsRes.Cells(Rows.Count, 1).End(xlUp).Offset(2)
Tgt.Value = "Result " & i - 1
With Sheets(i)
Set FRange = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 3)
FRange.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=CrRange, CopyToRange:=Tgt.Offset(1), Unique:=False
End With
Next
End Sub

naris
08-31-2008, 11:11 PM
Thanks you very mauch, that's great ...