Macro that copies data and inserts it into another worksheet

jpl73

New member
Joined
Sep 10, 2012
Messages
21
Reaction score
0
Points
0
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("D6:D25")
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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

New member
Joined
Sep 10, 2012
Messages
21
Reaction score
0
Points
0
The empty cells are at the end of the range.

Sent from my ADR6325 using Tapatalk
 

jpl73

New member
Joined
Sep 10, 2012
Messages
21
Reaction score
0
Points
0
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
 
Top