Search code examples
excelvbacopy-paste

VBA Paste Value to Next Empty Cell in a Range

I'm trying to copy and paste a value from one sheet (C39 on Sheet1) to the next empty cell within a range on another sheet (B6 through B18 on Sheet2). Here's the code I'm using.

Sheets("Sheet1").Range("C39").Copy
With Sheets("Sheet2").Range("B6:B18").End(xlUp).Offset(1)
.PasteSpecial Paste:=xlPasteValues
End With
End Sub

When I run this macro, it continues to overwrite B6 on Sheet2. Ideally, it'd see there's a value in B6 and paste into B7, and then B8, etc. What can I do to correct this?

Solution

  • Need to start at B16 in Sheet2 and look upwards:

    Sub dural()
    Dim r1 As Range, r2 As Range
    Set r1 = Sheets("Sheet1").Range("C39")
    Set r2 = Sheets("Sheet2").Range("B16").End(xlUp).Offset(1, 0)
    r1.Copy r2
End Sub

    (similar for PasteSpecial)