I want to copy a range of cells from one sheet to another sheet in the next blank row

Question

My workbook consists of two sheets. The first is used to enter data into "New Entry" and the other is a log "log" of all the entries over time. The "New Entry" Sheet is cleared so the next data set can be entered.

I need to copy the data from the "New Entry" Sheet into the "Log" Sheet in the next blank cell.

Here is what i have so far. My first issue is that sometimes the range that it is copying has blank cells, and i do not want to copy those. I only wnat to copy cells with values in that range.

My other issue is that I cannot get the copied cells to paste into the next blank cell.

I am not experienced in this and i have been working on this for over a week!

    Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("New Entry")
Set pasteSheet = Worksheets("Log")

copySheet.Range("H35:AY45").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

check out this solution: stackoverflow.com/questions/14495769/… — John Bustos
Thanks John. I actually tried to use that way too, but it would only copy over the first column of data and would not add to the next blank row after the previous entry. do you know why? — Shawn F

C Jones C Jones · Accepted Answer · 2014-02-26T05:06:00

So from looking at your question, if cekk H36 is blank, then H37 should take its place in logsheet. This does mean that if you were youre values would become skewed if you were matching them against the columns that their in, in New Entry.

If coulmn I has 5 non blank Cells, and column J has 8, then it will only copy those 5 and 8. However next time it will begin pasting column I in I7 and pasting in cell J10. Either way let me know what you think of this.

  Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim i As Long, j As Long
j = 8
i = 35
Set copySheet = Worksheets("New Entry")
Set pasteSheet = Worksheets("Log")
Do While j < 45
Do While i < 46
If Sheets("New Entry").Cells(i, j).Value <> "" Then
copySheet.Cells(i, j).Copy
pasteSheet.Cells(Rows.Count, j - 7).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
i = i + 1
Loop
i = 35
j = j + 1
Loop
End Sub

I want to copy a range of cells from one sheet to another sheet in the next blank row

1 Answers