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.

 

[Dannyh1]

Hi - how would this be triggered?

The way I read it, cell A1, would always have text in because you are copying into row A.

 

[JackDanIce]

Welcome to the board.

For example, you are saying to begin with column A is empty.

You then enter text into (e.g.) cell A10

You want a macro to copy range B1:E2, insert two rows above A10 and paste, so your entered text is now in cell A12?

But now you have three cells in column A that has text in it (assuming B1 and B2 is NOT empty), so should the macro insert two rows above cell A10 and two rows above A11 and copy and paste? This would then mean you have 7 cells in column A with data so copy and paste again?

Please clarify or provide an example.

 

[Dannyh1]

Apologies, I mis-read post, but more clarification is needed

 

[jrich1]

Some cells in Column A have data and I would like a macro that can identify those cells and insert two full blank rows above them. Then the data copied from range B1:E2 would be pasted in the corresponding columns in the new blank rows. Does that help to explain?

 

[JackDanIce]

Unfortunately not.

Do you want the worksheet to automatically do what you're asking?

Example

- There already is data in cell A10 and the macro puts the two rows above and copies and pastes the data

- NOW there is data in cells A10:A12 - should the macro automatically do what you ask?

In otherwords, are the cells in column A going to be static or be changing?

Try to read through my previous post and answer that

 

[jrich1]

Here's what it would look like before:
COLUMN A COL B COL C COL D COL E
Comparison Worksheet
Paytype 2011 2010 Chng
17. FedEx Express follows its PSP philosophy 62 63 -1
18. Have enough training to do my job well 79 93 -14
Left Blank N 288 273 15
1. My manager listens to my concerns 84 78 6
2. My manager tells me what is expected 86 84 2


Here's what it would look like after:
COLUMN A COL B COL C COL D COL E
Comparison Worksheet
Paytype 2011 2010 Chng
17. FedEx Express follows its PSP philosophy 62 63 -1
18. Have enough training to do my job well 79 93 -14
Comparison Worksheet
Paytype 2011 2010 Chng
Left Blank N 288 273 15
1. My manager listens to my concerns 84 78 6
2. My manager tells me what is expected 86 84 2

 

[Datsmart]

See if this does what you are asking:
Code copies B1:E2 into newly inserted rows when column A is not blank.
Code starts at bottom of Column B and works up to Row 3.

Sub Insert2Rows()
Dim LastRow As Long
    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")
        End If
    Next I
End Sub

 

[jrich1]

Thank you so much Datsmart!! That was exactly what I needed.

 

[JackDanIce]

A tiny modification to speed up Datsmart macro is:

Sub Insert2Rows()
 
Dim LastRow As Long, I as Long
Application.ScreenUpdating = False
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    LastRow = Cells(LastRow, "A").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")
           I = Cells(I, "A").End(xlUp).Row + 1
           If I < 3 Then Exit For
        End If
    Next I
Application.ScreenUpdating = True
End Sub

Depending on the size of your data, you may (or may not) notice any difference...