Let me see if I cannot help a little.

First - there's a problem in doing it exactly the way you want: buttons
don't know what row they're associated with. You might think that you could
write some code that would maybe test the name of the button and figure out
what row it is associated with by its name, but as you delete rows on the
first sheet, that plan will fail after the first time a button is used that
is farther down the sheet than a row that was deleted earlier.

It is much, MUCH easier to put one button on the sheet and have one macro
associated with it that decides which row to delete based on the row that a
cell on the sheet that you've chosen is on. You could put the button up at
the top of the sheet and make row 1 a little taller (so that the button fits
within row 1), and then use Window | Freeze to keep row 1 from scrolling as
you move down the sheet. That keeps the button available to you at all times.

How the code below works: you click/choose one or more cells in a single row
and then click the button. The code makes sur you haven't chosen several
rows and also confirms you want to delete the row, just in case you
accidentally clicked the button. If you reply yes, it copies the row into
the other sheet at row 5 and deletes it on the main sheet. You can control
what row it's copied to by changing a const (constant) value in the code, and
you'll need to change "Sheet2" to the name of the sheet you want to move data
into in the code. First the code, then how to create the button and get it
to work for you:

Open your workbook and press [Alt]+[F11] to open the VB Editor. In the VBE
menu, choose Insert | Module. Copy the code below into the provided code
module and make any changes (row number, sheet name) to it. Close the VBE.

Sub CopyAndDeleteRow()
Const moveToSheetRowNumber = 5 ' always put in row 5
Const moveToSheetName = "Sheet2" 'change?
Dim moveToSheet As Worksheet

'confirm only one row selected, although
'more than one cell in the row may be
'selected
If Selection.Rows.Count 1 Then
MsgBox "You may only move/delete 1 row at a time."
Exit Sub ' just quit
End If
'confirm user wants to delete the row
If MsgBox("Do you want to move & delete row #" _
& Selection.Row, vbYesNo, "Confirm") < vbYes Then
'they did not respond with [YES]
Exit Sub
End If
Selection.EntireRow.Copy
Set moveToSheet = Worksheets(moveToSheetName)
'insert data into a new empty row on the "new" sheet
moveToSheet.Range("A" & moveToSheetRowNumber).Insert
'delete the entire old row
Selection.EntireRow.Delete
End Sub

Next, select the sheet that will have the rows that are to be copied and
deleted. From the Excel menu, choose View | Toolbars | Forms
We will use the command button from the Forms toolbar instead of the
Controls toolbar because it's really easy to set it up to use the code we
just added to the workbook.

Click the Command Button control on the Forms tool bar and draw it on your
sheet - you can move it if you dont' get it exactly where you want it. As
soon as it is drawn a window will pop up showing you a list of macros in the
workbook and you simply click on the "CopyAndDeleteRow" entry to tell it to
use that code when it is clicked. Change the text on the button to something
meaningful and you're done! The one button with the single code routine will
do the work on the whole sheet.

"engbe" wrote:

Hi, Can any one please help me with writing a code in visual basics?

What i am trying to do is this:
I want to create a button called 'close' in row 1(sheet 1) and when I click
this I want a marco to run which will delete the entire row that the button
is on and then paste that row into row 5 in worksheet 2. I want to repeat
this for the next row however, when the second row is pasted into worksheet 2
i want the second row to be pasted in row 5 and the first row move down to
row 6...

I hope this makes sense and would really appreciate any assistance from
anyone as i am not to familiar with writing up codes...THANK YOU in advance!