Command button to post data from one sheet to another one

Closed
Report
Posts
22
Registration date
Friday October 19, 2012
Status
Member
Last seen
June 22, 2013
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
Hello,
I would be very grateful if some one van help me!

I have excel table with 10 columns (from A-J) in sheet1.
I fill data in the table and in column (I) I update the data in that row (I put the voucher No when updated). So what I need, if column(I) is filled with data and I clicked the command button (named "Post") I need the VBA code to cut the entire row (the criterea that column(I) in not empty) and paste it to the second sheet and for sure to delet that empty row in sheet1.

Thank you in advance,
Issa Hamameh

1 reply

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Issa,

Here you go:
Private Sub cmdPost_Click()
Dim lRow, x As Integer

lRow = Range("I" & Rows.Count).End(xlUp).Row

For Each cell In Range("I2:I" & lRow)
    If cell.Value <> vbNullString Then
        cell.EntireRow.Copy
        Sheets("Sheet2").Select
        Sheets("Sheet2").Range("I" & Rows.Count).End(xlUp).Offset(1, -8).PasteSpecial
    End If
Next cell

For x = lRow To 2 Step -1
    If Range("I" & x) <> vbNullString Then Range("I" & x).EntireRow.Delete
Next x

Application.CutCopyMode = False
End Sub

I Hope you like the result.

Best regards,
Trowa
1