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