Macro that copies data and inserts it into another worksheet

[jpl73]

I have a excel workbook with several sheets, These sheets get deleted by a macro that I created but before I delete them I want to copy a range of cells and insert them into another sheet that will keep data for six months or longer. I have a macro started and it works for what I want but it also copies the cells that are left blank and also inserts them. Here is what I have so far, all I need is when it copies the range the blank ones are left out. Any help would be greatly appreciated.

Thank you in advance

Sub PostData()

Dim CopyRange As Range, NextCell As Range

Set CopyRange = Sheet2.Range("D625")
Set NextCell = Sheet5.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)

CopyRange.Copy
NextCell.Insert Shift:=xlDown
Application.CutCopyMode = False

End Sub

 

[Bob Phillips]

Are the empty cells interspersed within the range (makes the job complex), or a contiguous set of cells at the end of the range (much easier)?

 

[jpl73]

The empty cells are at the end of the range.

Sent from my ADR6325 using Tapatalk

 

[jpl73]

I figured out a way to sort out the blanks as long as the blanks are at the end of the range. The code that is posted below works for me. It sorts data from blanks and inserts them into sheet 2.

Sub SortInsert()

'Sorts out blanks from the bottom of the given range and inserts them into sheet 2

Range("A1:A20").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$20").AutoFilter Field:=1, Criteria1:="<>"
Selection.Copy
Sheets("Sheet2").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.AutoFilter
End Sub