Loop to copy-insert Row For all names in a column

[peee2]

Hi All
Column BL9 TO BL25 contains names (Sometimes there might not be any names). Task is if it contains some names then to copy row(A9 TO AO9) and insert that Row xlDown and copy a name in cell B9 xlDown (the name being same as the one in column BL9 TO BL25. SO LOOP IT FOR ALL THE NAMES. copy and insert column and
copy corresponding names. Can somone pls point out the mistake in the below macro. thanks

Code
Dim i As Long

For i = 9 To Rows.count
For Each Cell In Range("BL9:BL25")
If Cell.Value <> "" Then

Range("A9:AO9").Select
Selection.Copy
Selection.End(xlDown).Select
Selection.Insert Shift:=xlDown
Range("BL8").Select
ActiveCell.Offset(1, 0).Copy
Range("B9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


End If


Next


End Sub


Code

 

[DanteAmor]

To help, I need to know step by step what you want to do.
If the macro does not work for you, then I can not guide myself with the macro.


If you can put a couple of examples, explaining how you have the information before the macro and an example of how your data would look after a macro execution. That would help a lot.

 

[peee2]

Hello Sir. I dont have permission to attach files.

 

[peee2]

Example of Data..(actually there are 500-600 rows with formulas)
A B C D E ......till AO
1 Copy
2 Nik
3 Badla


Den i get this data ..say in column BL, in same worksheet
BL
1 San
2 Sdp
3 Shr
4 Nay

Have to convert to this..
Final output should look like
A
1 Copy
2 Nik
3 San
4 Sdp
5 Shr
6 Nay
7 Badla


everyday i have to add new rows between last and second last row..ie in dis example between Nik and Badla. Sometimes 2 rows, sometime 40 rows and also SOMETIMES NOTHING. For this i copy the A TO AO ROW (because it has some formulas) and insert it between last and second last row and copy the name from the column BF to new row added(because from the names it picks up some numbers).. so have to do this for every name in column BF..
so can u guys pls help me.

 

[DanteAmor]

Example of Data..(actually there are 500-600 rows with formulas)
A B C D E ......till AO
1 Copy
2 Nik
3 Badla


Den i get this data ..say in column BL, in same worksheet
BL
1 San
2 Sdp
3 Shr
4 Nay

Have to convert to this..
Final output should look like
A
1 Copy
2 Nik
3 San
4 Sdp
5 Shr
6 Nay
7 Badla


everyday i have to add new rows between last and second last row..ie in dis example between Nik and Badla. Sometimes 2 rows, sometime 40 rows and also SOMETIMES NOTHING. For this i copy the A TO AO ROW (because it has some formulas) and insert it between last and second last row and copy the name from the column BF to new row added(because from the names it picks up some numbers).. so have to do this for every name in column BF..
so can u guys pls help me.

Try this

Sub Macro1()
    Dim lr As Long, cn As Long


    Application.ScreenUpdating = False
    lr = Range("BL" & Rows.Count).End(xlUp).Row
    If lr < 9 Then
        MsgBox "Nothing to copy"
        Exit Sub
    End If
    cn = WorksheetFunction.CountA(Range("BL9:BL" & lr))
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & lr & ":AO" & lr).Copy
    Range("A" & lr & ":AO" & lr + cn - 1).Insert Shift:=xlDown ', CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A" & lr).Resize(cn).Value = Range("BL9").Resize(cn).Value
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub