Hi
How do I separate each sort order and move to next blank column with heading
and also how I automatically change range of data.


Sub changeorder()
Range("A4").Select
Range("A1:A110").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2).EntireColumn
End Sub

See if you can modify these to your needs.
Sub CopyFilter()
'by Tom Ogilvy
Dim rng As Range
Dim rng2 As Range

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Sheet2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
ActiveSheet.ShowAllData

End Sub

Sub FilterData()
'by Bob Phillips
Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
With Range("B1:B" & iLastRow)
.AutoFilter Field:=1, Criteria1:="SC"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="SU"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("Sheet3").Range("A1")
.AutoFilter
End With
End Sub

Mike F
"****al shah" wrote in message
...
Hi
How do I separate each sort order and move to next blank column with
heading
and also how I automatically change range of data.


Sub changeorder()
Range("A4").Select
Range("A1:A110").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=
_
xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2).EntireColumn
End Sub

Thanks Mike Fogleman for reply
When i run Sub copyfilter() it gives Runtime error '91'
Object Variable or with Black Variable not set.
My data is like....

Date,Group,Name,In,Out.
I am sorting data as per Group

Any idea.
Thanks

****al

"Mike Fogleman" wrote:

See if you can modify these to your needs.
Sub CopyFilter()
'by Tom Ogilvy
Dim rng As Range
Dim rng2 As Range

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Sheet2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
ActiveSheet.ShowAllData

End Sub

Sub FilterData()
'by Bob Phillips
Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
With Range("B1:B" & iLastRow)
.AutoFilter Field:=1, Criteria1:="SC"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="SU"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("Sheet3").Range("A1")
.AutoFilter
End With
End Sub

Mike F
"****al shah" wrote in message
...
Hi
How do I separate each sort order and move to next blank column with
heading
and also how I automatically change range of data.


Sub changeorder()
Range("A4").Select
Range("A1:A110").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=
_
xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2).EntireColumn
End Sub

You mean on this line:
With ActiveSheet.AutoFilter.Range

that is probably because you don't have an autofilter on your data.

I am not sure what you want to do and Mike probably was just guessing as
well. Perhaps a clearer statement of your problem.

Your macro should work - what part do you want to change. Just make the area
in column 1 dynamic?

Sub changeorder()
Range("A4").Select
set rng = Range(Range("A1"),Range("A1").end(xldown))
rng.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2)
End Sub


--
Regards,
Tom Ogilvy



"****al shah" wrote in message
...
Thanks Mike Fogleman for reply
When i run Sub copyfilter() it gives Runtime error '91'
Object Variable or with Black Variable not set.
My data is like....

Date,Group,Name,In,Out.
I am sorting data as per Group

Any idea.
Thanks

****al

"Mike Fogleman" wrote:

See if you can modify these to your needs.
Sub CopyFilter()
'by Tom Ogilvy
Dim rng As Range
Dim rng2 As Range

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Sheet2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
ActiveSheet.ShowAllData

End Sub

Sub FilterData()
'by Bob Phillips
Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
With Range("B1:B" & iLastRow)
.AutoFilter Field:=1, Criteria1:="SC"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="SU"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("Sheet3").Range("A1")
.AutoFilter
End With
End Sub

Mike F
"****al shah" wrote in message
...
Hi
How do I separate each sort order and move to next blank column with
heading
and also how I automatically change range of data.


Sub changeorder()
Range("A4").Select
Range("A1:A110").Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=
_
xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2).EntireColumn
End Sub

Thnaks Tom Ogilvy
the code which i have given is working fine. But i have a problem is that
when i Sort data than after when sort order change it should move that data
into next column.
Any help
thanks Again
****al

"Tom Ogilvy" wrote:

You mean on this line:
With ActiveSheet.AutoFilter.Range

that is probably because you don't have an autofilter on your data.

I am not sure what you want to do and Mike probably was just guessing as
well. Perhaps a clearer statement of your problem.

Your macro should work - what part do you want to change. Just make the area
in column 1 dynamic?

Sub changeorder()
Range("A4").Select
set rng = Range(Range("A1"),Range("A1").end(xldown))
rng.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2)
End Sub


--
Regards,
Tom Ogilvy



"****al shah" wrote in message
...
Thanks Mike Fogleman for reply
When i run Sub copyfilter() it gives Runtime error '91'
Object Variable or with Black Variable not set.
My data is like....

Date,Group,Name,In,Out.
I am sorting data as per Group

Any idea.
Thanks

****al

"Mike Fogleman" wrote:

See if you can modify these to your needs.
Sub CopyFilter()
'by Tom Ogilvy
Dim rng As Range
Dim rng2 As Range

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Sheet2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
ActiveSheet.ShowAllData

End Sub

Sub FilterData()
'by Bob Phillips
Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
With Range("B1:B" & iLastRow)
.AutoFilter Field:=1, Criteria1:="SC"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="SU"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("Sheet3").Range("A1")
.AutoFilter
End With
End Sub

Mike F
"****al shah" wrote in message
...
Hi
How do I separate each sort order and move to next blank column with
heading
and also how I automatically change range of data.


Sub changeorder()
Range("A4").Select
Range("A1:A110").Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=
_
xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2).EntireColumn
End Sub

It does that now. Each time you run the macro, it should sort the data and
copy the sorted data to the next available column.

If you want to copy the old data before you sort it, move this line to the
top of the macro:

Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2)


--
Regards,
Tom Ogilvy

"****al shah" wrote in message
...
Thnaks Tom Ogilvy
the code which i have given is working fine. But i have a problem is that
when i Sort data than after when sort order change it should move that
data
into next column.
Any help
thanks Again
****al

"Tom Ogilvy" wrote:

You mean on this line:
With ActiveSheet.AutoFilter.Range

that is probably because you don't have an autofilter on your data.

I am not sure what you want to do and Mike probably was just guessing as
well. Perhaps a clearer statement of your problem.

Your macro should work - what part do you want to change. Just make the
area
in column 1 dynamic?

Sub changeorder()
Range("A4").Select
set rng = Range(Range("A1"),Range("A1").end(xldown))
rng.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2)
End Sub


--
Regards,
Tom Ogilvy



"****al shah" wrote in message
...
Thanks Mike Fogleman for reply
When i run Sub copyfilter() it gives Runtime error '91'
Object Variable or with Black Variable not set.
My data is like....

Date,Group,Name,In,Out.
I am sorting data as per Group

Any idea.
Thanks

****al

"Mike Fogleman" wrote:

See if you can modify these to your needs.
Sub CopyFilter()
'by Tom Ogilvy
Dim rng As Range
Dim rng2 As Range

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Sheet2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
ActiveSheet.ShowAllData

End Sub

Sub FilterData()
'by Bob Phillips
Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
With Range("B1:B" & iLastRow)
.AutoFilter Field:=1, Criteria1:="SC"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="SU"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("Sheet3").Range("A1")
.AutoFilter
End With
End Sub

Mike F
"****al shah" wrote in
message
...
Hi
How do I separate each sort order and move to next blank column
with
heading
and also how I automatically change range of data.


Sub changeorder()
Range("A4").Select
Range("A1:A110").Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=
_
xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2).EntireColumn
End Sub

thanks again Tom But what i want is .... E.G.

If sort Region East,west,north and south
Than When Esat is over than only East from the List should move from the
list to next column and afteronly North move to next coulmn ...and so on...

Any help

****al

"Tom Ogilvy" wrote:

It does that now. Each time you run the macro, it should sort the data and
copy the sorted data to the next available column.

If you want to copy the old data before you sort it, move this line to the
top of the macro:

Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2)


--
Regards,
Tom Ogilvy

"****al shah" wrote in message
...
Thnaks Tom Ogilvy
the code which i have given is working fine. But i have a problem is that
when i Sort data than after when sort order change it should move that
data
into next column.
Any help
thanks Again
****al

"Tom Ogilvy" wrote:

You mean on this line:
With ActiveSheet.AutoFilter.Range

that is probably because you don't have an autofilter on your data.

I am not sure what you want to do and Mike probably was just guessing as
well. Perhaps a clearer statement of your problem.

Your macro should work - what part do you want to change. Just make the
area
in column 1 dynamic?

Sub changeorder()
Range("A4").Select
set rng = Range(Range("A1"),Range("A1").end(xldown))
rng.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2)
End Sub


--
Regards,
Tom Ogilvy



"****al shah" wrote in message
...
Thanks Mike Fogleman for reply
When i run Sub copyfilter() it gives Runtime error '91'
Object Variable or with Black Variable not set.
My data is like....

Date,Group,Name,In,Out.
I am sorting data as per Group

Any idea.
Thanks

****al

"Mike Fogleman" wrote:

See if you can modify these to your needs.
Sub CopyFilter()
'by Tom Ogilvy
Dim rng As Range
Dim rng2 As Range

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Sheet2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
ActiveSheet.ShowAllData

End Sub

Sub FilterData()
'by Bob Phillips
Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
With Range("B1:B" & iLastRow)
.AutoFilter Field:=1, Criteria1:="SC"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="SU"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("Sheet3").Range("A1")
.AutoFilter
End With
End Sub

Mike F
"****al shah" wrote in
message
...
Hi
How do I separate each sort order and move to next blank column
with
heading
and also how I automatically change range of data.


Sub changeorder()
Range("A4").Select
Range("A1:A110").Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=
_
xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(1).Copy Cells(1, 256).End(xlToLeft)(1, 2).EntireColumn
End Sub