A macro to tell excel to input to next empty cell

[Guest]

This is my code, and it works, but now I need to close the empty spaces. For
example, I have approximately 10 possible ranges check for contents and if
they contain text, then to copy and paste into an area on my spreadsheet that
can only accommodate 5 cells. Not all of the 10 will contain text -- say 3
out of 10 will actually hold text, or 2 out of 10, etc., but for those that
do hold text, I want to see the values in the 5 cells or less. Is there a
way to accomplish this?

Sheets("Morning Report Database").Select
Range("Y2").Select
If ActiveCell.Value <> "" Then
Selection.Copy
Sheets("CHEATSHEET").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False

ElseIf ActiveCell = "" Then
ActiveCell.Offset(RowOffset).Activate
End If

Sheets("Morning Report Database").Select
Range("Y3").Select
If ActiveCell.Value <> "" Then
Selection.Copy
Sheets("CHEATSHEET").Select
Range("B7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False

ElseIf ActiveCell = "" Then
ActiveCell.Offset(RowOffset).Activate
End If

 

[Tom Ogilvy]

Something along the lines of

icol = 1
for each cell in 10CellRange
if not isempty(cell) then
worksheets(2).Cells(irow,icol).Value = cell.value
icol = icol + 1
end if
Next