Paste into next free row

[seang87]

Hi All,

I have the following running on inserted check boxes, which I'm mostly happy with. The issue I have is the values paste to sheet 2 and override the previous values.

I need the values to paste in the next free empty row on sheet2, there may be others rows in sheet2 with values that need to remain. But I need this to look and paste in the next free empty row.


Here is the example, I will have around 30 check boxes through the sheet

Sub T2_Click()


Dim LastRow1 As Long
Dim LastRow As Long
Dim LastCol As Long
Dim sh As Worksheet



Set sh = Sheets("Sheet1")



LastCol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LastRow1 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row


Range(sh.Cells(2, 1), sh.Cells(2, "Q")).Copy Destination:=Sheets("Sheet2").Range("A" & LastRow1 + 1)
Range(sh.Cells(2, 1), sh.Cells(2, "P")).ClearContents



End Sub






Sub T3_Click()




Dim LastRow1 As Long
Dim LastRow As Long
Dim LastCol As Long
Dim sh As Worksheet

Set sh = Sheets("Sheet1")

LastCol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LastRow1 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row


Range(sh.Cells(2, 1), sh.Cells(3, "Q")).Copy Destination:=Sheets("Sheet2").Range("A" & LastRow1 + 1)
Range(sh.Cells(2, 1), sh.Cells(3, "P")).ClearContents


End Sub



Thanks!!!

 

[L. Howard]

Hi seang87,
Try the bold print line.

Regards,
Howard

Sub T2_Click()


 Dim LastRow1 As Long
 Dim LastRow As Long
 Dim LastCol As Long
 Dim sh As Worksheet



 Set sh = Sheets("Sheet1")



 LastCol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
 LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
 LastRow1 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row


' Range(sh.Cells(2, 1), sh.Cells(2, "Q")).Copy Destination:=Sheets("Sheet2").Range("A" & LastRow1 + 1)

 [B]Range(sh.Cells(2, 1), sh.Cells(2, "Q")).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2)

 Range(sh.Cells(2, 1), sh.Cells(2, "P")).ClearContents
[/B]


 End Sub

 

[seang87]

Hi L.Howard,

It is still pasting over the sheet2 data....

 

[seang87]

Hi L.Howard,

I was being thick, it's working now. Thanks!!ray:

 

[L. Howard]

Does sheet 2 have anything in column A.

The paste line code refers to column A, first blank cell coming up from the bottom.


Howard

Edit.. Just read your last post.

Great.

FYI: You can use (1) to copy over last entry, (2) to copy to first blank cell, (3) will leave a blank row between copies.

H