EXCEL macro code

December 2, 2011 at 08:57:58
Specs: Windows XP
I am trying to macro code a button "UPDATE" in excel where i want to it to function as if column M5:M50 has a "Y" in it then copy the entire row and paste it in sheet 2 and delete it from the old. I am new to coding and have no idea how to do this
my rows start with info at row 5 there is no end to how many i may have but i will say it probably will never hit 50 Please Help
the sheet i want it to paste is called SCG transfers

See More: EXCEL macro code

Report •

December 2, 2011 at 18:01:13
You didn't give us the name of the sheet that contains the original data, so I used Source Sheet in this code. Before you run it, you'll need to change every occurrence of Source Sheet to the name of your sheet, or change the name of your sheet to Source Sheet.

I strongly suggest that you try this code in a backup copy of your bookwork since it deletes rows and those deletions can not be undone.

Sub MoveY()
'Determine how many rows of data in Source Sheet
  lastRw = Sheets("Source Sheet").Range("M" & Rows.Count).End(xlUp).Row
'Loop through rows
   For nxtY = 5 To lastRw
'If there is a Y in Column M, determine the next
'empty row in SCG transfers and copy the Row
    If Sheets("Source Sheet").Range("M" & nxtY) = "Y" Then
      nxtRw = Sheets("SCG transfers").Range("M" & Rows.Count).End(xlUp).Row + 1
       Sheets("Source Sheet").Range("M" & nxtY).EntireRow.Copy _
        Destination:=Sheets("SCG transfers").Range("A" & nxtRw)
    End If
'Loop through Source Sheet rows in reverse order so that they can be deleted
   For nxtY = lastRw To 5 Step -1
'If there is a Y in Column M, delete the Row
     If Sheets("Source Sheet").Range("M" & nxtY) = "Y" Then
        Sheets("Source Sheet").Range("M" & nxtY).EntireRow.Delete
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question