Command button to post data from one sheet to another one

Closed
issahamameh Posts 22 Registration date Friday October 19, 2012 Status Member Last seen June 22, 2013 - Jun 22, 2013 at 03:53 PM
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 - Jun 24, 2013 at 11:34 AM
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

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 523
Jun 24, 2013 at 11:34 AM
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