PDA

View Full Version : [SOLVED] VBA - Paste to First Empty Cell in Range



grimes869
06-07-2017, 06:12 AM
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
06-07-2017, 07:07 AM
With Sheet1
For Each FoundCell In Rng
Set tgt = .Cells(17, "H").End(xlUp)(2)
FoundCell.Resize(, 6).Copy tgt
Next
End With

grimes869
06-07-2017, 07:15 AM
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
06-07-2017, 07:34 AM
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