Help please...

[jrich1]

I am new to writing macros and need to come up with something to do the following:

Anytime there is text in a cell in column A, insert two rows above the cell with text, copy the range B1:E2 and paste this in the two new rows.

 

[Datsmart]

"error code '9'- subscript out of range" generally means VBA can not find the worksheet name listed in your code.
Are you sure "101322", "101325" exist?
Dim your worksheet as "Worksheet", not "Sheets"

Dim wks As Worksheet

JackDanIce has some good suggestions to speed code.

Be aware, as written, his code will work on every worksheet in your workbook.

 

[JackDanIce]

Spotted some errors, try:

Sub Insert2Rows()
 
Dim LastRow As Long, i as Long, j as long

Application.ScreenUpdating = False
For j = 1 to Worksheets.count
  With Sheets(j)
    LastRow = .Cells(Rows.Count, 2).End(xlUp).Row
    LastRow = .Cells(LastRow, 1).End(xlUp).Row
    For i = LastRow To 3 Step -1
        If .Cells(i, 1) <> "" Then
            .Range(.Cells(i, 1), .Cells(i + 1, 1)).EntireRow.Insert
            .Range("B1:E2").Copy Cells(i, 2)
           .Cells(i + 1, 1) = .Cells(i + 2, 1) & .Cells(i + 2, 1)
           i = .Cells(I, 1).End(xlUp).Row + 1
           If i < 3 Then Exit For
        End If
    Next i
  End With
Next j
Application.ScreenUpdating = True
End Sub

If that doesn't work, happy for you to PM me and send me your workbook for me to work out what's wrong

 

[JackDanIce]

I misread multiple worksheets as every sheet - my bad! Thanks for spot John

 

[jrich1]

this is good though - ultimatly i need the code to work on every sheet. so this seems easier b/c i don't have to name each sheet in the macro.

 

[JackDanIce]

So the code works or you're still having problems?

 

[Datsmart]

The following code will work with all worksheets EXCEPT the ones listed in the code:

Sub ExceptThese()
'Loop through all worksheets except listed
    For Each wks In ActiveWorkbook.Worksheets
        'Do this for all sheets except these
        Select Case wks.Name
            Case "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"
                'do nothing with the above worksheets
            Case Else
                'with all other worksheets, do the following...
                With wks
                    Sheets(wks.Name).Activate
                    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
                    For i = LastRow To 3 Step -1
                        If Cells(i, "A") <> "" Then
                            Range(Cells(i, 1), Cells(i + 1, 1)).EntireRow.Insert
                            Range("B1:E2").Copy Cells(i, "B")
                            Cells(i + 1, "A").Value = Cells(i + 2, "A").Value & Cells(i + 2, "B").Value
                        End If
                    Next i
                End With
        End Select
    Next wks
End Sub