Copy and paste value to another cell if blank

Question

I am very new to macro and trying to use it for our daily production sheet. I have created a macro to copy the values sheet1 to sheet2.

Range("B3").Select
Selection.Copy
Sheets("sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True

what I need is if sheet2 cell A1 is not blank then paste values need to be in range A2.

hope I explain well.

score 1 · Accepted Answer · answered Apr 14 '22 at 13:56

Just check the value of A1 if it is empty vbNullString.

' define worksheets
Dim wsSrc As Worksheet
Set wsSrc = ThisWorkbook.Worksheets("sheet1")

Dim wsDest As Worksheet
Set wsDest = ThisWorkbook.Worksheets("sheet2")

If wsDest.Range("A1").Value = vbNullString Then
    ' If A1 is empty copy value to A1
    wsDest.Range("A1").Value = wsSrc.Range("B3").Value
Else
    ' otherwise copy it to A2
    wsDest.Range("A2").Value = wsSrc.Range("B3").Value
End If

You might benefit from reading How to avoid using Select in Excel VBA.

Copy and paste value to another cell if blank

1 Answers1