copy but don't overlay

**mwc0914** · 06-15-2005, 01:47 PM

Is there a way via a macro to copy the values in a range of cells to another range of cells, but if there is anything in the destination range of cells, do not overlay with the values you are copying from?

Consolidating would work, except I would need my destination cell range to be one of the consolidation cell ranges.

**tkstock** · 06-15-2005, 02:11 PM

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("A1:B50") ' Your copy range
Set rng2 = Range("C1:D50") ' Your paste range
For Each x In rng2.Cells
   If x.Value <> "" Then Exit Sub
Next x
rng1.Copy
rng2.PasteSpecial xlPasteAll

HTH

**mwc0914** · 06-15-2005, 02:42 PM

Thanks, but it did not work.

I have attached an example spreadsheet with the macro in it. Maybe I am missing something. I want to be able to copy cells E1:G3 to A1:C3 but not overlay what is already in cell A1:C1.

**tkstock** · 06-15-2005, 02:59 PM

    Dim rng1 As Range
    Dim rng2 As Range
    Set rng1 = Range("e1:g3") ' Your copy range
    Set rng2 = Range("a1:c3") ' Your paste range
    r1 = rng1.Rows.Count
    c1 = rng1.Columns.Count

    For i = 1 To r1
        For j = 1 To c1
            If rng2.Cells(i, j) = "" Then
                rng2.Cells(i, j) = rng1.Cells(i, j)
            End If
        Next j
    Next i

That work for you? I thought if your paste range contained ANYTHING you didn't want to paste anything. If you are just filling in, this should work.