Previous Topic Next Topic

Command button to post data from one sheet to another one

Closed
issahamameh Posts 22 Registration date Friday 19 October 2012 Status Member Last seen 22 June 2013 - 22 Jun 2013 à 15:53
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 24 Jun 2013 à 11:34
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 response

Réponse 1 / 1
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
24 Jun 2013 à 11:34
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

Similar discussions

Copying data from one Excel sheet to another.
Remedial -
Kav -

30 responses
Ig stories from close friends to public
Darren -
zara -

31 responses
How to copy sheets from one work book to other
Sultaaan -
venkat1926 -

1 response