Deleting and Copying Marco code

[engbe]

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!

 

[JLatham]

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.

 

[JLatham]

I changed the code a little, this might be better for you. This lets you
"protect" certain rows that you may not want to be deleted using the button.
It also does some cleanup at the end to release resources back to the system.

Sub CopyAndDeleteRow()
Const moveToSheetRowNumber = 5 ' always put in row 5
Const moveToSheetName = "Sheet2" 'change?
Const saveRows = 2 ' 1st row # that can be deleted
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
'don't delete row(s) with lower numbers than
'the value of saveRows - this will allow you
'to make sure "permanent" information is preserved.
'I've set it to 2 so that you can't delete row 1
'which presumably contains labels and the button.
If Selection.Row < saveRows Then
MsgBox "This row cannot be deleted with this feature"
Exit Sub
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
Set moveToSheet = Nothing ' cleanup
End Sub

 

[engbe]

Dear JLatham,

YOU ARE AWESOME! Thank you so much for your help! The code worked
perfectly!!!! I really really appreciate the time and effort you put in to
creating the code and helping someone that you don't even know...it is so
nice to know that there are genuine people like you in the world! : )

I changed the code a little, this might be better for you. This lets you
"protect" certain rows that you may not want to be deleted using the button.
It also does some cleanup at the end to release resources back to the system.

Sub CopyAndDeleteRow()
Const moveToSheetRowNumber = 5 ' always put in row 5
Const moveToSheetName = "Sheet2" 'change?
Const saveRows = 2 ' 1st row # that can be deleted
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
'don't delete row(s) with lower numbers than
'the value of saveRows - this will allow you
'to make sure "permanent" information is preserved.
'I've set it to 2 so that you can't delete row 1
'which presumably contains labels and the button.
If Selection.Row < saveRows Then
MsgBox "This row cannot be deleted with this feature"
Exit Sub
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
Set moveToSheet = Nothing ' cleanup
End Sub

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!

 

[JLatham]

Glad that things worked as well for you as they did here. You're quite
welcome, and I'm definitely not the only person around here trying to help
.... this place is just full of them.

Dear JLatham,

YOU ARE AWESOME! Thank you so much for your help! The code worked
perfectly!!!! I really really appreciate the time and effort you put in to
creating the code and helping someone that you don't even know...it is so
nice to know that there are genuine people like you in the world! : )

I changed the code a little, this might be better for you. This lets you
"protect" certain rows that you may not want to be deleted using the button.
It also does some cleanup at the end to release resources back to the system.

Sub CopyAndDeleteRow()
Const moveToSheetRowNumber = 5 ' always put in row 5
Const moveToSheetName = "Sheet2" 'change?
Const saveRows = 2 ' 1st row # that can be deleted
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
'don't delete row(s) with lower numbers than
'the value of saveRows - this will allow you
'to make sure "permanent" information is preserved.
'I've set it to 2 so that you can't delete row 1
'which presumably contains labels and the button.
If Selection.Row < saveRows Then
MsgBox "This row cannot be deleted with this feature"
Exit Sub
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
Set moveToSheet = Nothing ' cleanup
End Sub

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!