How to copy from another sheet for the cells if they are only blank

Question

What I want to do?

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

How can I do that?

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

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

I want to copy the last 7 cells.

I wrote such a code, yet did not work.

Any idea? The code I write 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

Copy pasting a range from 1 sheet to another is very fast. So how does it make a difference? Simply copy A1:A27 and paste over A1:A20 :) It is max 2 lines of code (depending on how you want to copy) — Siddharth Rout
No, but I want to do it dynamically: I want to run macro to create reports. This was just a small sample to depict what I need. Think that I have 8 columns from 55 columns to copy, and each column will be another column in my report. — Ahmet
Run your code step by step and tell us what is the exact issue that you are facing, then we can help you. — Raunak Thomas
Can you amend your question with the exact requirement. Be as descriptive as possible. Use screenshots to show sample data if you want. This way we can give you an exact reply :) — Siddharth Rout
@RaunakThomas I am so novice that I do not even know how to run and show here. — Ahmet

Harassed Dad Harassed Dad · Accepted Answer · 2018-04-16T10:51:54

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

How to copy from another sheet for the cells if they are only blank

1 Answers