require help

[VBABEGINER]

In my column B there are no. of records present and My all records started from b7.
In col B there are some codes whose naming convetions started from "IC".
this are located any where.
I want to put them (started them) from B7 only.
suppose, if records are 3 IC's code then it will automatically insert the rows from b7 and go to b8 , b9 and so on.
i coded following

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i As Long
Dim x As Integer, y As Integer
Application.ScreenUpdating = False
For x = 7 To Range("B7").End(xlDown).Row

For i = 8 To Range("B6").End(xlDown).Row

If Left(Cells(i, 2), 2) = "IC" Then
Selection.Cut
Cells(x, 2).Select
ActiveSheet.Paste
End If

Next
Next x
End Sub

can any one pls suggest me, how i can put them in proper format?

 

[VBABEGINER]

no one is there to help?

 

[VBABEGINER]

I have little updated the code

Dim IC As Long
Dim x As Integer

For x = 8 To 10
For IC = 11 To Range("B11").End(xlDown).Row

If Left(Cells(i, 2), 2) = "IC" Then
Cells(x, 2).Select = Left(Cells(i, 2), 2).Value
'Selection.Insert Shift:=xlDown

Next x
Next

Is this right?

 

[littleiitin]

Try this

Sub FindandReplace()
    Range("B7:B" & Range("B" & Rows.Count).End(xlUp).Row).Replace What:="IC", Replacement:="_IC", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("B7:B" & Range("B" & Rows.Count).End(xlUp).Row)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B7:B" & Range("B" & Rows.Count).End(xlUp).Row).Replace What:="_IC", Replacement:="IC", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[VBABEGINER]

Hey Hi,
Thank You for your solution.

This is not giving any o/p.
The code which u had given me, i made minor changes.
there are multiple sheet available.
The red mark area giving the error.

We have already data in excel, only we need remove the the "IC" notations at the starting i-e b8 (before i was using on b7)

So experts, what i can make here, for great solution?

Sub FindandReplace()
Dim k
For k = 1 To Worksheets.Count
Worksheets.Item(k).Activate
If ((UCase(Worksheets.Item(k).Name) = "Sheet1")) Then
Worksheets.Item(k).Tab.ColorIndex = 4
Else
Set xlwksh = ThisWorkbook.Worksheets(k)
xlwksh.Name = ThisWorkbook.Worksheets(k).Cells(1, 3) & "_" & ThisWorkbook.Worksheets(k).Cells(6, 2)

Range("B8:B" & Range("B" & Rows.Count).End(xlUp).Row).Replace What:="IC", Replacement:="_IC", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

ActiveWorkbook.Worksheets(k).Sort.SortFields.Clear

ActiveWorkbook.Worksheets(k).Sort.SortFields.Add Key:=Range("B8"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B8:B" & Range("B" & Rows.Count).End(xlUp).Row)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("B8:B" & Range("B" & Rows.Count).End(xlUp).Row).Replace What:="_IC", Replacement:="IC", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
Next k
End Sub

 

[VBABEGINER]

 

[littleiitin]

Try this:

Sub FindandReplace()
Dim k
For k = 1 To Worksheets.Count
Worksheets.Item(k).Activate
If ((UCase(Worksheets.Item(k).Name) = "SHEET1")) Then
    Worksheets.Item(k).Tab.ColorIndex = 4
Else
    Set xlwksh = ThisWorkbook.Worksheets(k)
    With xlwksh
        .Name = .Cells(1, 3) & "_" & .Cells(6, 2)
    .Range("B8:B" & .Range("B" & Rows.Count).End(xlUp).Row).Replace What:="IC", Replacement:="_IC", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    '.Sort.SortFields.Clear
    
    .Sort.SortFields.Add Key:=Range("B8"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    
    With .Sort
        .SetRange Range("B8:B" & Range("B" & Rows.Count).End(xlUp).Row)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    .Range("B8:B" & .Range("B" & Rows.Count).End(xlUp).Row).Replace What:="_IC", Replacement:="IC", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    End With
End If
Next k
End Sub