VBA - Paste to First Empty Cell in Range

[grimes869]

I am working on a sheet that needs to be able to pull data & paste it into the first empty row of a defined range (C6:H16). I would typically use a count from the bottom, however, as there is data below where I need to paste it hasn't been working.

The idea is to find all related objects, select the related row, and move it to the empty range.

I am quite new to Excel, so bear with me. Here's the section I'm working on and what I have so far:

With Sheet1
     For Each FoundCell In rng
            FoundCell.Select            
           Range(Cells(Selection.Row, 3), Cells(Selection.Row, 8)).Select            
            Selection.Copy
            ~(Find range and paste to next empty row in Range C6:H16)
     Next
End With 

[mdmackillop]

With Sheet1
     For Each FoundCell In Rng
        Set tgt = .Cells(17, "H").End(xlUp)(2)
        FoundCell.Resize(, 6).Copy tgt
     Next
End With

[grimes869]

With Sheet1
     For Each FoundCell In Rng
        Set tgt = .Cells(17, "H").End(xlUp)(2)
        FoundCell.Resize(, 6).Copy tgt
     Next
End With

This works great. Is there a way to move the selection's values only (without reformatting the destination?)

[mdmackillop]

With Sheet1 
    For Each FoundCell In Rng 
        Set tgt = .Cells(17, "H").End(xlUp)(2) 
        FoundCell.Resize(, 6).Copy
        tgt.PasteSpecial xlValues 
    Next 
End With