Excel Macros Cut & Paste?

Question

Hi, I need help with macros.

I need to learn how to cut an active row from one worksheet and paste it into to the next available blank row on another worksheet using a macro. I've bought a couple of books but still can't figure it out.

Any help appreciated.

Thanks.

Answer 1

You don't state the criteria for determining which row to cut and paste, but it is usually based on the value in a particular column.



You request a macro, but usually it is better to make the process automatic when the specified criteria is met in a given row in the specified column. That can be done using an event handler.



So, assuming you wish to cut and paste the entire row if the value in column E is 'Yes', here is one way, using the Workbook_Change event handler.



Copy the following event handler to the clipboard (highlight the entire event handler, right click inside the highlighted area, and 'Copy'):



Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, LastRow

LastRow = Range("E" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow

If UCase(Cells(i, "E").Value) = "YES" Then

Cells(i, "E").EntireRow.Cut Destination:= _

Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

End If

Next

End Sub



Select the worksheet containing the rows to cut and paste, and right click the sheet tab at the bottom.



Select 'View Code'.



Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').



Close the VBE (red button - top right).



Now, whenever a cell in column E contains the word 'Yes' (or YES, yes, or even YeS), the entire row will be cut and pasted into the next available row in Sheet2.



Note: if your column is not E, change the three "E" references to your column reference, i.e. "M", "T", etc.



If the sheet to copy to is not Sheet2, change the "Sheet2" reference to your sheet name to copy to, i.e. "Extract", "Archive", etc.

Answer 2

You need to explain your question a little better. What you are wanting to do can be done. However, it is sort of unclear what you are wanting to do. I can write macros to create new tabs that say: Baseball, Football, Hockey, Basketball. And I can write one where the Baseball worksheet has the columns Yankees and Mets. I can also write one where it takes info you enter and puts the info in the right column in the right worksheet. However, macros are used to do stuff on a regular basis not like a one time thing. So if you are going to be consistently adding tabs or columns it makes sense. But there usually is only a limited number of teams. So it normally makes sense to create the worksheets and columns yourself. As far as entering the data in the right spot, that is something you might want to have a macro do for you. Because the data you have to enter on a constant basis. So you might want to have one spot you enter the data. Then the macro just puts the data in the right spot. The only thing is. You haven't specified where the person would enter the data. Would that be on the first worksheet? Are they going to enter it through a VBA Form? And then in one spot you say you want the data in columns. Then later below you say you want it in rows. Programming a macro takes a bunch of specifics. And you haven't provided many specifics.

Answer 3

The following macro will achieve

Highlight the first cell to be copied then run the macro

Problem; the paste routine will overwrite previous entries

Sub MyCutPaste()

Range(Selection, Selection.End(xlToRight)).Select

Selection.Cut

Sheets("Sheet2").Select

Range("C4").Select (note: always to the same cell)

ActiveSheet.Paste

Range("A1").Select

End Sub



Overcome the overwrite

Sub MyCutPaste()

Range(Selection, Selection.End(xlToRight)).Select

Selection.Cut

Sheets("Sheet2").Select

Range("C4").Select

Do While ActiveCell <> Blank (find next empty cell)

ActiveCell.Offset(1, 0).Select

Loop

ActiveSheet.Paste

Range("A1").Select

End Sub

Answer 4

Here you go try this. Also I would try Ctrl+X for cut and Ctrl+V for paste



http://office.microsoft.com/en-us/training/get-in-the-loop-with-excel-macros-RZ001150634.aspx