display specific data on seperate sheets
Hi
Try this:
Sub SplitData()
Dim TargetRange As Range
Dim myArr()
Dim counter As Long
Set TargetSh = Worksheets("Sheet1")
Set TargetRange = TargetSh.Range("A1", TargetSh.Range("A" &
Rows.Count).End(xlUp))
TargetRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
UniqueNames = TargetRange.SpecialCells(xlCellTypeVisible).Cells. Count
ReDim myArr(UniqueNames - 1)
For Each cell In TargetRange.SpecialCells(xlCellTypeVisible)
myArr(counter) = cell.Value
counter = counter + 1
Next
ActiveSheet.ShowAllData
For sh = 1 To UBound(myArr)
Set DestSh = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
DestSh.Name = myArr(sh)
TargetRange.AutoFilter field:=1, Criteria1:=myArr(sh)
TargetRange.SpecialCells(xlCellTypeVisible).Entire Row.Copy
DestSh.Range("A1")
TargetRange.AutoFilter
Next
End Sub
Regards,
Per
"Sam" skrev i meddelelsen
...
Hi All,
I have a Sheet with this kind of data:
ColumnA ColumnB Column C
Pam tree a asdf
Tom low b adf
Sam bree c adf
Pam tree d adf
Jim cree e agaad
Sam bree f adfgg
Pam tree g djg
Tom low h djhfh
Sam bree i ertr
How can I display Tom low's data only on Sheet2, Sam bree's data only on
Sheet3 and so on?
Thanks in advance
|