Search code examples
vbaexcelcopy-paste

How can I copy from another sheet for the cells if they are only blank?


I want to compare two sheets and if the row in sheet1 empty, then copy the same row from sheet(project).

My goal is to copy the new entries at Column A from sheet("Project") to sheet1 BUT only the new entries!

For example in sheet1 A1:A20 already not blank. In sheet("project"), A1:A27 are NOT Blank.

I want to copy the last 7 cells.

The code I wrote copies all the rows. I want to copy the rows which if they are blank in sheet1, then copy those rows from sheet(project).

Sub CopyD()

    Dim lst As Long

    Sheets("Project").Select

    Range("A1:A10000").Select

    Application.CutCopyMode = False

    Selection.Copy

    With Sheets("Sheet1")
        lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("A" & lst).PasteSpecial xlPasteColumnWidths
        .Range("A" & lst).PasteSpecial xlPasteValues
    End With

End Sub

Solution

  • Sub CopyNewRows
    
    Dim x as long
    dim y as long
    with worksheets("Sheet1")
    x = .cells(.rows.count,1).end(xlup).row  'last occupied row in col A
    end with
    with  worksheets("Project")
    y =.cells(.rows.count,1).end(xlup).row 'last occupied row in source sheet
    .range(.cells(x+1,1),.cells(y,1)).entirerow.copy worksheets("sheet1").cells(x+1,1)
    end with
    end sub
    

    This assumes that column A is always filled for each occupied row