Automatically move entire row to the next open row on another sheet based on cell value

Sorry guys, very new to this. Here is my issue.

I have a workbook with twosheets.

Sheet 1 - has seven columns of data starting at cell A2 through G2. If there is any numbers in cell E2, I want the data from all seven cells to be move over to Sheet 2 beginning at A11 to G11. I want this to be done automatically. I want this to be continued under the same principle for cells B3 through G3, B4 through G4 etc.


Sheet 1.JPGSheet 2.JPG

Any help will be greatly appreciated.

Hello LongHornBlue,

The following code may do the job for you:-

Private Sub Worksheet_Calculate()

Application.ScreenUpdating = False

Dim lRow As Long

lRow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("A11:G" & Rows.Count).ClearContents

    For Each cell In Range("E2:E" & lRow)
    If cell.Value <> 0 Then
    cell.EntireRow.Copy
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End If
Next
    
Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub

It is a Worksheet_Calculate event and needs to be placed in the worksheet module rather than a standard module. So, right click on the sheet 1 tab, select "View Code" and in the big white field that appears, paste the code.


Every time that you enter a value in Column E on sheet 1 and click away, the subject row will be transferred to sheet 2 (I'm assuming that you have a simple sum formula dragged down Column G). Have a look at my attached test work book to see what I mean. I also noticed that the headings in the two sheets do not match exactly. Is this a typo? The test work book has them matching.

Let us know how it goes.

Cheerio,
vcoolio.

It worked perfectly! Absolutely perfect! Thank you very much as it was EXACTLY what I needed.

Hello LongHornBlue,

Glad that I could help.

Cheerio,
vcoolio.