none
cut and paste row data to new sheet and delete the blank row in first shhet RRS feed

  • Question

  • i have one sheet "Al Shab" and when ever column 17 of each row is "completed"  drop down box data validation list "completed" &" pending"  and i have created one shape update, when i click update all the row with column 17 value= completed shouldbe cut and paste in sheet"completed" and the blank row in the first sheet should be deleted .we should cut that entire row and paste it in another sheet "completed" when ever i click update is should paste to next available column in completed sheet.

    below is the code please help me to delete the blank cell in the first sheet AL shab.

    Sub FlowchartSequentialAccessStorage1_Click()
    a = Worksheets("Al Shab").Cells(Rows.Count, 1).End(xlUp).Row
    For I = 4 To a

    If Worksheets("Al Shab").Cells(I, 17).Value = "COMPLETE" Then
    Worksheets("Al Shab").Rows(I).Cut

    Worksheets("COMPLETED").Activate
    b = Worksheets("COMPLETED").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("COMPLETED").Cells(b + 1, 1).Select
    ActiveSheet.Paste


    End If
    Next
    End Sub

    Monday, December 10, 2018 1:58 PM

Answers

  • I have tested the code with

        For i = a To 4 Step -1

    and it worked as intended. Are you sure that column A is populated? If not, change

        a = Worksheets("Al Shab").Cells(Rows.Count, 1).End(xlUp).Row

    to

        a = Worksheets("Al Shab").Cells(Rows.Count, 17).End(xlUp).Row


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by DEEPU1980 Tuesday, December 11, 2018 6:38 AM
    Monday, December 10, 2018 8:53 PM

All replies

  • Try this:

    Sub FlowchartSequentialAccessStorage1_Click()
        Dim a As Long, b As Long, i As Long
        a = Worksheets("Al Shab").Cells(Rows.Count, 1).End(xlUp).Row
        For i = 4 To a
            If Worksheets("Al Shab").Cells(i, 17).Value = "COMPLETE" Then
                b = Worksheets("COMPLETED").Cells(Rows.Count, 1).End(xlUp).Row
                Worksheets("Al Shab").Rows(i).Cut Worksheets("COMPLETED").Cells(b + 1, 1)
                Worksheets("Al Shab").Rows(i).Delete
            End If
        Next i
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 10, 2018 4:12 PM
  • some rows in sheet Al Shab is not cut and paste in second sheet
    Monday, December 10, 2018 4:35 PM
  • Does it work better if you change

        For i = 4 To a

    to

        For i = a To 4 Step -1


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 10, 2018 4:36 PM
  • not working at all
    Monday, December 10, 2018 5:10 PM
  • can we delete the blank cells at last after all the data has being cut and paste to second page since i think deletinon of row in between is affection the rowcounts sometimes
    Monday, December 10, 2018 5:15 PM
  • can we delete the blank cells at last after all the data has being cut and paste to second page since i think deletinon of row in between is affection the rowcounts sometimes
    Monday, December 10, 2018 5:16 PM
  • I have tested the code with

        For i = a To 4 Step -1

    and it worked as intended. Are you sure that column A is populated? If not, change

        a = Worksheets("Al Shab").Cells(Rows.Count, 1).End(xlUp).Row

    to

        a = Worksheets("Al Shab").Cells(Rows.Count, 17).End(xlUp).Row


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by DEEPU1980 Tuesday, December 11, 2018 6:38 AM
    Monday, December 10, 2018 8:53 PM
  • ITS WORKING FINE THANKS
    Tuesday, December 11, 2018 6:38 AM