i have a list of data and column I contain name. i want to cut records that
contains certain name and paste it after all the rest of the data on the same
worksheet.how can i do that??

"violet" wrote:

i have a list of data and column I contain name. i want to cut records that
contains certain name and paste it after all the rest of the data on the same
worksheet.how can i do that??

here is my code.currently this code problem is that when a row is being cut
n paste, it will actually skip the next row for checking. in this case i
will have records that fit the criteria to be missed out. anyone can edit for
me so that i can get what i want.or anyone can teach me hw to select all
records that fit the criteria as a range and paste them at 1 go.

Sub sorting()
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, compareRange As Range
Dim x As Integer, i As Integer, numofRows As Integer

'Identify the corret column for comparing
Dim setwks As Worksheet
Set setwks = Worksheets("Settings")
Dim lngcol As Variant, lngreqcol As Variant, lngcomparecol As Variant
Dim iLastRow As Long
Dim namecol As String, strReqtopcell As String, strReqbotcell As
String, _
strcomparecell As String

namecol = setwks.Cells(3, 2).Value
lngcol = Asc(namecol) - Asc("A")
lngreqcol = lngcol + Asc("A")
strReqtopcell = Chr(lngreqcol) + "2"
strReqbotcell = Chr(lngreqcol) + "65536"


Set topCel = Range(strReqtopcell)
Set bottomCel = Range(strReqbotcell).End(xlUp)
If topCel.Row bottomCel.Row Then Exit Sub ' test if source range is empty
Set sourceRange = Range(topCel, bottomCel)
numofRows = sourceRange.Rows.Count

numofRows = sourceRange.Rows.Count
For i = 1 To numofRows
If sourceRange(i) = "Full Time Equivalents" Then
Rows(i + 1).Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(iLastRow + 1).Select
Selection.Insert Shift:=xlDown

Else

End If
Next
End Sub

Violet
When you are looping through a range from top to bottom and
deleting/inserting rows as you go, you will get what you are getting. What
you should do is loop from bottom to top. Your statement:
For i = 1 To numofRows
Should be changed to:
For i = numofRows To 1 Step -1
HTH Otto

"violet" wrote in message
...


"violet" wrote:

i have a list of data and column I contain name. i want to cut records
that
contains certain name and paste it after all the rest of the data on the
same
worksheet.how can i do that??

here is my code.currently this code problem is that when a row is being
cut
n paste, it will actually skip the next row for checking. in this case i
will have records that fit the criteria to be missed out. anyone can edit
for
me so that i can get what i want.or anyone can teach me hw to select all
records that fit the criteria as a range and paste them at 1 go.

Sub sorting()
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, compareRange As Range
Dim x As Integer, i As Integer, numofRows As Integer

'Identify the corret column for comparing
Dim setwks As Worksheet
Set setwks = Worksheets("Settings")
Dim lngcol As Variant, lngreqcol As Variant, lngcomparecol As Variant
Dim iLastRow As Long
Dim namecol As String, strReqtopcell As String, strReqbotcell As
String, _
strcomparecell As String

namecol = setwks.Cells(3, 2).Value
lngcol = Asc(namecol) - Asc("A")
lngreqcol = lngcol + Asc("A")
strReqtopcell = Chr(lngreqcol) + "2"
strReqbotcell = Chr(lngreqcol) + "65536"


Set topCel = Range(strReqtopcell)
Set bottomCel = Range(strReqbotcell).End(xlUp)
If topCel.Row bottomCel.Row Then Exit Sub ' test if source range is
empty
Set sourceRange = Range(topCel, bottomCel)
numofRows = sourceRange.Rows.Count

numofRows = sourceRange.Rows.Count
For i = 1 To numofRows
If sourceRange(i) = "Full Time Equivalents" Then
Rows(i + 1).Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(iLastRow + 1).Select
Selection.Insert Shift:=xlDown

Else

End If
Next
End Sub

well..thanks.it works..but this now actually have a problem. i need a few
row space in between my orginal data and paste data..if i change to
rows(lastrow+2) den my paste data will also have space. how can i solve this.

"Otto Moehrbach" wrote:

Violet
When you are looping through a range from top to bottom and
deleting/inserting rows as you go, you will get what you are getting. What
you should do is loop from bottom to top. Your statement:
For i = 1 To numofRows
Should be changed to:
For i = numofRows To 1 Step -1
HTH Otto

"violet" wrote in message
...


"violet" wrote:

i have a list of data and column I contain name. i want to cut records
that
contains certain name and paste it after all the rest of the data on the
same
worksheet.how can i do that??

here is my code.currently this code problem is that when a row is being
cut
n paste, it will actually skip the next row for checking. in this case i
will have records that fit the criteria to be missed out. anyone can edit
for
me so that i can get what i want.or anyone can teach me hw to select all
records that fit the criteria as a range and paste them at 1 go.

Sub sorting()
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, compareRange As Range
Dim x As Integer, i As Integer, numofRows As Integer

'Identify the corret column for comparing
Dim setwks As Worksheet
Set setwks = Worksheets("Settings")
Dim lngcol As Variant, lngreqcol As Variant, lngcomparecol As Variant
Dim iLastRow As Long
Dim namecol As String, strReqtopcell As String, strReqbotcell As
String, _
strcomparecell As String

namecol = setwks.Cells(3, 2).Value
lngcol = Asc(namecol) - Asc("A")
lngreqcol = lngcol + Asc("A")
strReqtopcell = Chr(lngreqcol) + "2"
strReqbotcell = Chr(lngreqcol) + "65536"


Set topCel = Range(strReqtopcell)
Set bottomCel = Range(strReqbotcell).End(xlUp)
If topCel.Row bottomCel.Row Then Exit Sub ' test if source range is
empty
Set sourceRange = Range(topCel, bottomCel)
numofRows = sourceRange.Rows.Count

numofRows = sourceRange.Rows.Count
For i = 1 To numofRows
If sourceRange(i) = "Full Time Equivalents" Then
Rows(i + 1).Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(iLastRow + 1).Select
Selection.Insert Shift:=xlDown

Else

End If
Next
End Sub

Violet
Tell me in words what this macro is supposed to do. In other words,
describe the task at hand. Then tell me in words what your macro actually
does. Otto
"violet" wrote in message
...
well..thanks.it works..but this now actually have a problem. i need a few
row space in between my orginal data and paste data..if i change to
rows(lastrow+2) den my paste data will also have space. how can i solve
this.

"Otto Moehrbach" wrote:

Violet
When you are looping through a range from top to bottom and
deleting/inserting rows as you go, you will get what you are getting.
What
you should do is loop from bottom to top. Your statement:
For i = 1 To numofRows
Should be changed to:
For i = numofRows To 1 Step -1
HTH Otto

"violet" wrote in message
...


"violet" wrote:

i have a list of data and column I contain name. i want to cut records
that
contains certain name and paste it after all the rest of the data on
the
same
worksheet.how can i do that??

here is my code.currently this code problem is that when a row is being
cut
n paste, it will actually skip the next row for checking. in this case
i
will have records that fit the criteria to be missed out. anyone can
edit
for
me so that i can get what i want.or anyone can teach me hw to select
all
records that fit the criteria as a range and paste them at 1 go.

Sub sorting()
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, compareRange As Range
Dim x As Integer, i As Integer, numofRows As Integer

'Identify the corret column for comparing
Dim setwks As Worksheet
Set setwks = Worksheets("Settings")
Dim lngcol As Variant, lngreqcol As Variant, lngcomparecol As
Variant
Dim iLastRow As Long
Dim namecol As String, strReqtopcell As String, strReqbotcell As
String, _
strcomparecell As String

namecol = setwks.Cells(3, 2).Value
lngcol = Asc(namecol) - Asc("A")
lngreqcol = lngcol + Asc("A")
strReqtopcell = Chr(lngreqcol) + "2"
strReqbotcell = Chr(lngreqcol) + "65536"


Set topCel = Range(strReqtopcell)
Set bottomCel = Range(strReqbotcell).End(xlUp)
If topCel.Row bottomCel.Row Then Exit Sub ' test if source range is
empty
Set sourceRange = Range(topCel, bottomCel)
numofRows = sourceRange.Rows.Count

numofRows = sourceRange.Rows.Count
For i = 1 To numofRows
If sourceRange(i) = "Full Time Equivalents" Then
Rows(i + 1).Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(iLastRow + 1).Select
Selection.Insert Shift:=xlDown

Else

End If
Next
End Sub

the tasks actually is to cut n paste data that contain full time equivalents
to the bottom of all data. i need to do a sub total for data that does not
contain full time equi. and den a sub total for full time equi n finally a
total for 2 data. thus i tink a need some row in between the data n teh data
that contain full time equi. this macro now cut n paste the data to the last
row of data mean it will leave no space in between. this is what i want get:

name jan feb march
alan 5 10 15
ben 6 0 3
sum 11 10 18

full time 5 10 15
Full time 1 2 2
sum 6 12 17
total 17 22 35


"Otto Moehrbach" wrote:

Violet
Tell me in words what this macro is supposed to do. In other words,
describe the task at hand. Then tell me in words what your macro actually
does. Otto
"violet" wrote in message
...
well..thanks.it works..but this now actually have a problem. i need a few
row space in between my orginal data and paste data..if i change to
rows(lastrow+2) den my paste data will also have space. how can i solve
this.

"Otto Moehrbach" wrote:

Violet
When you are looping through a range from top to bottom and
deleting/inserting rows as you go, you will get what you are getting.
What
you should do is loop from bottom to top. Your statement:
For i = 1 To numofRows
Should be changed to:
For i = numofRows To 1 Step -1
HTH Otto

"violet" wrote in message
...


"violet" wrote:

i have a list of data and column I contain name. i want to cut records
that
contains certain name and paste it after all the rest of the data on
the
same
worksheet.how can i do that??

here is my code.currently this code problem is that when a row is being
cut
n paste, it will actually skip the next row for checking. in this case
i
will have records that fit the criteria to be missed out. anyone can
edit
for
me so that i can get what i want.or anyone can teach me hw to select
all
records that fit the criteria as a range and paste them at 1 go.

Sub sorting()
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, compareRange As Range
Dim x As Integer, i As Integer, numofRows As Integer

'Identify the corret column for comparing
Dim setwks As Worksheet
Set setwks = Worksheets("Settings")
Dim lngcol As Variant, lngreqcol As Variant, lngcomparecol As
Variant
Dim iLastRow As Long
Dim namecol As String, strReqtopcell As String, strReqbotcell As
String, _
strcomparecell As String

namecol = setwks.Cells(3, 2).Value
lngcol = Asc(namecol) - Asc("A")
lngreqcol = lngcol + Asc("A")
strReqtopcell = Chr(lngreqcol) + "2"
strReqbotcell = Chr(lngreqcol) + "65536"


Set topCel = Range(strReqtopcell)
Set bottomCel = Range(strReqbotcell).End(xlUp)
If topCel.Row bottomCel.Row Then Exit Sub ' test if source range is
empty
Set sourceRange = Range(topCel, bottomCel)
numofRows = sourceRange.Rows.Count

numofRows = sourceRange.Rows.Count
For i = 1 To numofRows
If sourceRange(i) = "Full Time Equivalents" Then
Rows(i + 1).Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(iLastRow + 1).Select
Selection.Insert Shift:=xlDown

Else

End If
Next
End Sub

Violet
The code that you provided in your previous post does not do any
copy/cut/paste at all. Did you include all the code?
The code that you posted essentially does nothing more than set a range
(sourceRange) until it reaches the "For" loop. Within the "For" loop, the
code checks each cell in that range to see if the cell contains "Full Time
Equivalents". If it does, the code then inserts a blank row below the last
row of the data. That's all the code does. There are no cut/copy/paste
commands anywhere in the code. Am I missing something? HTH Otto
"violet" wrote in message
...
the tasks actually is to cut n paste data that contain full time
equivalents
to the bottom of all data. i need to do a sub total for data that does not
contain full time equi. and den a sub total for full time equi n finally a
total for 2 data. thus i tink a need some row in between the data n teh
data
that contain full time equi. this macro now cut n paste the data to the
last
row of data mean it will leave no space in between. this is what i want
get:

name jan feb march
alan 5 10 15
ben 6 0 3
sum 11 10 18

full time 5 10 15
Full time 1 2 2
sum 6 12 17
total 17 22 35


"Otto Moehrbach" wrote:

Violet
Tell me in words what this macro is supposed to do. In other words,
describe the task at hand. Then tell me in words what your macro
actually
does. Otto
"violet" wrote in message
...
well..thanks.it works..but this now actually have a problem. i need a
few
row space in between my orginal data and paste data..if i change to
rows(lastrow+2) den my paste data will also have space. how can i solve
this.

"Otto Moehrbach" wrote:

Violet
When you are looping through a range from top to bottom and
deleting/inserting rows as you go, you will get what you are getting.
What
you should do is loop from bottom to top. Your statement:
For i = 1 To numofRows
Should be changed to:
For i = numofRows To 1 Step -1
HTH Otto

"violet" wrote in message
...


"violet" wrote:

i have a list of data and column I contain name. i want to cut
records
that
contains certain name and paste it after all the rest of the data
on
the
same
worksheet.how can i do that??

here is my code.currently this code problem is that when a row is
being
cut
n paste, it will actually skip the next row for checking. in this
case
i
will have records that fit the criteria to be missed out. anyone can
edit
for
me so that i can get what i want.or anyone can teach me hw to select
all
records that fit the criteria as a range and paste them at 1 go.

Sub sorting()
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, compareRange As Range
Dim x As Integer, i As Integer, numofRows As Integer

'Identify the corret column for comparing
Dim setwks As Worksheet
Set setwks = Worksheets("Settings")
Dim lngcol As Variant, lngreqcol As Variant, lngcomparecol As
Variant
Dim iLastRow As Long
Dim namecol As String, strReqtopcell As String, strReqbotcell As
String, _
strcomparecell As String

namecol = setwks.Cells(3, 2).Value
lngcol = Asc(namecol) - Asc("A")
lngreqcol = lngcol + Asc("A")
strReqtopcell = Chr(lngreqcol) + "2"
strReqbotcell = Chr(lngreqcol) + "65536"


Set topCel = Range(strReqtopcell)
Set bottomCel = Range(strReqbotcell).End(xlUp)
If topCel.Row bottomCel.Row Then Exit Sub ' test if source range
is
empty
Set sourceRange = Range(topCel, bottomCel)
numofRows = sourceRange.Rows.Count

numofRows = sourceRange.Rows.Count
For i = 1 To numofRows
If sourceRange(i) = "Full Time Equivalents" Then
Rows(i + 1).Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(iLastRow + 1).Select
Selection.Insert Shift:=xlDown

Else

End If
Next
End Sub

oh..sorry..i miss out one line there..anyway ur loop from bottom works
well..actually this code is gd enough already as my another code which do sub
total will auto insert line for the subtotal so no worry. but nw when i try
to apply this to many worksheet, it doents work. i follow what i have done in
the past to apply macro to mulitple worksheet but it wun work. did i miss out
anything?


Public Sub Workbook_Open()

Dim wkb As Workbook
Dim wks As Worksheet

Set wkb = ActiveWorkbook
For Each wks In wkb.Worksheets(Array("Koram", "Hong_Kong", "Korea",
"China", "Malaysia", "Brunei", "Indonesia", _
"Philippines", "Singapore", "Thailand", "Taiwan", "Vietnam", "HUB",
"India", "Sri_Lanka", "Bangladesh"))
Call sorting(wks)
Next wks

MsgBox ("Note: Please check the data for duplicate id or entries")
End Sub


Sub sorting(wks As Worksheet)
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, compareRange As Range
Dim x As Integer, i As Integer, numofRows As Integer

'Identify the corret column for comparing
Dim setwks As Worksheet
Set setwks = Worksheets("Settings")
Dim lngcol As Variant, lngreqcol As Variant, lngcomparecol As Variant

Dim namecol As String, strReqtopcell As String, strReqbotcell As
String, _
strcomparecell As String

namecol = setwks.Cells(3, 2).Value
lngcol = Asc(namecol) - Asc("A")
lngreqcol = lngcol + Asc("A")
strReqtopcell = Chr(lngreqcol) + "2"
strReqbotcell = Chr(lngreqcol) + "65536"


Set topCel = Range(strReqtopcell)
Set bottomCel = Range(strReqbotcell).End(xlUp)
If topCel.Row bottomCel.Row Then Exit Sub ' test if source range is empty
Set sourceRange = Range(topCel, bottomCel)
numofRows = sourceRange.Rows.Count

numofRows = sourceRange.Rows.Count
For i = numofRows To 1 Step -1
If sourceRange(i) = "Full Time Equivalents" Then
Rows(i + 1).Cut
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(iLastRow + 1).Select
Selection.Insert Shift:=xlDown


Else

End If
Next
End Sub


"Otto Moehrbach" wrote:

Violet
The code that you provided in your previous post does not do any
copy/cut/paste at all. Did you include all the code?
The code that you posted essentially does nothing more than set a range
(sourceRange) until it reaches the "For" loop. Within the "For" loop, the
code checks each cell in that range to see if the cell contains "Full Time
Equivalents". If it does, the code then inserts a blank row below the last
row of the data. That's all the code does. There are no cut/copy/paste
commands anywhere in the code. Am I missing something? HTH Otto
"violet" wrote in message
...
the tasks actually is to cut n paste data that contain full time
equivalents
to the bottom of all data. i need to do a sub total for data that does not
contain full time equi. and den a sub total for full time equi n finally a
total for 2 data. thus i tink a need some row in between the data n teh
data
that contain full time equi. this macro now cut n paste the data to the
last
row of data mean it will leave no space in between. this is what i want
get:

name jan feb march
alan 5 10 15
ben 6 0 3
sum 11 10 18

full time 5 10 15
Full time 1 2 2
sum 6 12 17
total 17 22 35


"Otto Moehrbach" wrote:

Violet
Tell me in words what this macro is supposed to do. In other words,
describe the task at hand. Then tell me in words what your macro
actually
does. Otto
"violet" wrote in message
...
well..thanks.it works..but this now actually have a problem. i need a
few
row space in between my orginal data and paste data..if i change to
rows(lastrow+2) den my paste data will also have space. how can i solve
this.

"Otto Moehrbach" wrote:

Violet
When you are looping through a range from top to bottom and
deleting/inserting rows as you go, you will get what you are getting.
What
you should do is loop from bottom to top. Your statement:
For i = 1 To numofRows
Should be changed to:
For i = numofRows To 1 Step -1
HTH Otto

"violet" wrote in message
...


"violet" wrote:

i have a list of data and column I contain name. i want to cut
records
that
contains certain name and paste it after all the rest of the data
on
the
same
worksheet.how can i do that??

here is my code.currently this code problem is that when a row is
being
cut
n paste, it will actually skip the next row for checking. in this
case
i
will have records that fit the criteria to be missed out. anyone can
edit
for
me so that i can get what i want.or anyone can teach me hw to select
all
records that fit the criteria as a range and paste them at 1 go.

Sub sorting()
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, compareRange As Range
Dim x As Integer, i As Integer, numofRows As Integer

'Identify the corret column for comparing
Dim setwks As Worksheet
Set setwks = Worksheets("Settings")
Dim lngcol As Variant, lngreqcol As Variant, lngcomparecol As
Variant
Dim iLastRow As Long
Dim namecol As String, strReqtopcell As String, strReqbotcell As
String, _
strcomparecell As String

namecol = setwks.Cells(3, 2).Value
lngcol = Asc(namecol) - Asc("A")
lngreqcol = lngcol + Asc("A")
strReqtopcell = Chr(lngreqcol) + "2"
strReqbotcell = Chr(lngreqcol) + "65536"


Set topCel = Range(strReqtopcell)
Set bottomCel = Range(strReqbotcell).End(xlUp)
If topCel.Row bottomCel.Row Then Exit Sub ' test if source range
is
empty
Set sourceRange = Range(topCel, bottomCel)
numofRows = sourceRange.Rows.Count

numofRows = sourceRange.Rows.Count
For i = 1 To numofRows
If sourceRange(i) = "Full Time Equivalents" Then
Rows(i + 1).Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(iLastRow + 1).Select
Selection.Insert Shift:=xlDown

Else

End If
Next
End Sub

Violet
What happens when you run that code? If you get an error message, what
is the stated error? When you click on the "Debug" button in the error box,
what line of code is highlighted? Otto
"violet" wrote in message
...
oh..sorry..i miss out one line there..anyway ur loop from bottom works
well..actually this code is gd enough already as my another code which do
sub
total will auto insert line for the subtotal so no worry. but nw when i
try
to apply this to many worksheet, it doents work. i follow what i have done
in
the past to apply macro to mulitple worksheet but it wun work. did i miss
out
anything?


Public Sub Workbook_Open()

Dim wkb As Workbook
Dim wks As Worksheet

Set wkb = ActiveWorkbook
For Each wks In wkb.Worksheets(Array("Koram", "Hong_Kong", "Korea",
"China", "Malaysia", "Brunei", "Indonesia", _
"Philippines", "Singapore", "Thailand", "Taiwan", "Vietnam", "HUB",
"India", "Sri_Lanka", "Bangladesh"))
Call sorting(wks)
Next wks

MsgBox ("Note: Please check the data for duplicate id or entries")
End Sub


Sub sorting(wks As Worksheet)
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, compareRange As Range
Dim x As Integer, i As Integer, numofRows As Integer

'Identify the corret column for comparing
Dim setwks As Worksheet
Set setwks = Worksheets("Settings")
Dim lngcol As Variant, lngreqcol As Variant, lngcomparecol As Variant

Dim namecol As String, strReqtopcell As String, strReqbotcell As
String, _
strcomparecell As String

namecol = setwks.Cells(3, 2).Value
lngcol = Asc(namecol) - Asc("A")
lngreqcol = lngcol + Asc("A")
strReqtopcell = Chr(lngreqcol) + "2"
strReqbotcell = Chr(lngreqcol) + "65536"


Set topCel = Range(strReqtopcell)
Set bottomCel = Range(strReqbotcell).End(xlUp)
If topCel.Row bottomCel.Row Then Exit Sub ' test if source range is
empty
Set sourceRange = Range(topCel, bottomCel)
numofRows = sourceRange.Rows.Count

numofRows = sourceRange.Rows.Count
For i = numofRows To 1 Step -1
If sourceRange(i) = "Full Time Equivalents" Then
Rows(i + 1).Cut
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(iLastRow + 1).Select
Selection.Insert Shift:=xlDown


Else

End If
Next
End Sub


"Otto Moehrbach" wrote:

Violet
The code that you provided in your previous post does not do any
copy/cut/paste at all. Did you include all the code?
The code that you posted essentially does nothing more than set a
range
(sourceRange) until it reaches the "For" loop. Within the "For" loop,
the
code checks each cell in that range to see if the cell contains "Full
Time
Equivalents". If it does, the code then inserts a blank row below the
last
row of the data. That's all the code does. There are no cut/copy/paste
commands anywhere in the code. Am I missing something? HTH Otto
"violet" wrote in message
...
the tasks actually is to cut n paste data that contain full time
equivalents
to the bottom of all data. i need to do a sub total for data that does
not
contain full time equi. and den a sub total for full time equi n
finally a
total for 2 data. thus i tink a need some row in between the data n teh
data
that contain full time equi. this macro now cut n paste the data to the
last
row of data mean it will leave no space in between. this is what i want
get:

name jan feb march
alan 5 10 15
ben 6 0 3
sum 11 10 18

full time 5 10 15
Full time 1 2 2
sum 6 12 17
total 17 22 35


"Otto Moehrbach" wrote:

Violet
Tell me in words what this macro is supposed to do. In other
words,
describe the task at hand. Then tell me in words what your macro
actually
does. Otto
"violet" wrote in message
...
well..thanks.it works..but this now actually have a problem. i need
a
few
row space in between my orginal data and paste data..if i change to
rows(lastrow+2) den my paste data will also have space. how can i
solve
this.

"Otto Moehrbach" wrote:

Violet
When you are looping through a range from top to bottom and
deleting/inserting rows as you go, you will get what you are
getting.
What
you should do is loop from bottom to top. Your statement:
For i = 1 To numofRows
Should be changed to:
For i = numofRows To 1 Step -1
HTH Otto

"violet" wrote in message
...


"violet" wrote:

i have a list of data and column I contain name. i want to cut
records
that
contains certain name and paste it after all the rest of the
data
on
the
same
worksheet.how can i do that??

here is my code.currently this code problem is that when a row is
being
cut
n paste, it will actually skip the next row for checking. in
this
case
i
will have records that fit the criteria to be missed out. anyone
can
edit
for
me so that i can get what i want.or anyone can teach me hw to
select
all
records that fit the criteria as a range and paste them at 1 go.

Sub sorting()
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, compareRange As Range
Dim x As Integer, i As Integer, numofRows As Integer

'Identify the corret column for comparing
Dim setwks As Worksheet
Set setwks = Worksheets("Settings")
Dim lngcol As Variant, lngreqcol As Variant, lngcomparecol As
Variant
Dim iLastRow As Long
Dim namecol As String, strReqtopcell As String, strReqbotcell
As
String, _
strcomparecell As String

namecol = setwks.Cells(3, 2).Value
lngcol = Asc(namecol) - Asc("A")
lngreqcol = lngcol + Asc("A")
strReqtopcell = Chr(lngreqcol) + "2"
strReqbotcell = Chr(lngreqcol) + "65536"


Set topCel = Range(strReqtopcell)
Set bottomCel = Range(strReqbotcell).End(xlUp)
If topCel.Row bottomCel.Row Then Exit Sub ' test if source
range
is
empty
Set sourceRange = Range(topCel, bottomCel)
numofRows = sourceRange.Rows.Count

numofRows = sourceRange.Rows.Count
For i = 1 To numofRows
If sourceRange(i) = "Full Time Equivalents" Then
Rows(i + 1).Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(iLastRow + 1).Select
Selection.Insert Shift:=xlDown

Else

End If
Next
End Sub