button

[fadymadhoun]

hi everyone,
i am not an excel professional, but i can manage.
i want to make a button at the end of a row that if clicked the whole row will move to the next sheet and the row will be deleted from the first worksheet
is this a possible thing to do?
i appreciate your help.
thank you very much

 

[MickG]

Try something like this:-

Private Sub CommandButton1_Click()
Dim Rw As Long
Rw = CommandButton1.TopLeftCell.Row
With Range("A" & Rw).EntireRow
    .Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Delete
End With
End Sub

 

[fadymadhoun]

thank you MickG for your help,
it actually works but not exactly like i want.
suppose you have 5 rows.
and the row number 4 is t he one you wanna move to the next spread sheet!
i think we shall have this button such like i choose the row and click on it then this specific row will move to the first available row in the next sheet, and the row below will move upwards.
is this alot of work?
thank you again!

 

[Sharid]

This will do it, it search column P if there is some thing in it then it will copy and paste that row/s in to the sheet called Dormant and delete from the source sheet. You can change column P to anything column you want, In this column have a dropdown list from which you can select Remove.

So if colum P have Remove in it it would copy and paste yjay row/rows to sheet Dormant and delete from source sheet

Private Sub CommandButton1_Click()

Dim LR As Long, NR As Long, a As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, "P").End(xlUp).Row ' look in this column before copying
NR = Worksheets("Dormant").Range("P" & Rows.Count).End(xlUp).Offset(1).Row

For a = LR To 10 Step -1 ' does not copy anything above row 10 change if needed

  If UCase(Range("P" & a)) <> "" And Range("P" & a) <> "" Then '
  'this will copy and paste in to new sheet, change your range and sheet name here
  Range("A" & a & ":CG" & a).Copy
Worksheets("Dormant").Range("A" & NR).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
    NR = Worksheets("Dormant").Range("P" & Rows.Count).End(xlUp).Offset(1).Row
    Worksheets("Dormant").Range("A" & NR & ":CG" & NR).Value = Range("A" & a & ":CG" & a).Value
    
    'this will delete from source sheet remove if you don't want i to delet from source sheet
    Range("P" & a).EntireRow.Delete
  End If
Next a
Application.ScreenUpdating = True

End Sub

 

[MickG]

Try this:-

Private Sub CommandButton2_Click()
Dim Ans As String
Ans = MsgBox("Is Row """ & Selection.Row & """ Correct", vbYesNo + vbInformation)
If Ans = vbYes Then
With Range("A" & Selection.Row).EntireRow
    .Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Delete
End With
End If
End Sub

 

[Sharid]

You can downlaod a quick demo from here

https://app.box.com/s/qezvq3b7i9lug1myj4dw46junnbpk8yt