Hi Archana,

Am Fri, 12 Apr 2013 07:43:10 -0700 (PDT) schrieb Archana:

But I don't want to create the different sheets, I want to move the data to different sheet with all of the groups like the Group1 header and data below and Group2 header and data below.....and so on.

And some times I have only 2 groups in the sheet and some times I have 10 groups.

in that case I agree with Harald. To create a Pivot table is the easiest
and quickest way. If you define a dynamic range name as source for the
table, you only need to refresh if data has changed.

But if you want your layout, then try:

Sub Filter()
Dim LRow As Long
Dim i As Integer, j As Integer
Dim myArr As Variant
Dim myCount As Integer

Application.ScreenUpdating = False
j = 1
With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1:A8").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("K1"), Unique:=True
myCount = .Cells(.Rows.Count, "K").End(xlUp).Row
myArr = .Range("K2:K" & myCount)
.Range("K1:K" & myCount).ClearContents
For i = LBound(myArr) To UBound(myArr)
.Range("A1:C" & LRow).AutoFilter _
field:=1, Criteria1:=myArr(i, 1)
Sheets("Sheet2").Cells(j, 1) = myArr(i, 1)
j = j + 1
.Range("B1:C" & LRow).Copy _
Sheets("Sheet2").Cells(j, 1)
j = j + myCount + 1
Next
..AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2