How to Move Row in Excel from one sheet to another

January 12, 2017 at 06:19:27
Specs: Windows 8.1 enterpirse
Could someone help me write a code for excel that will move a row of information from one sheet to another....please?? I have tried to use codes on other websites and I just don't understand it to make it that specific to my needs.

In my source spreadsheet (Intake), when yes is entered into column M I would like columns A to J moved into the target sheet (Consult) into columns F to O. The row in the source sheet will remain and not be deleted.

Thanks for your help!!


See More: How to Move Row in Excel from one sheet to another

Report •

#1
January 12, 2017 at 06:45:11
You can use the code below, it will copy columns A to J of the row where column M is changed to 'Yes' and paste it into sheets "Consult" columns F to O

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NextRow
    
    NextRow = Sheets("Consult").Range("F" & Rows.Count).End(xlUp).Row
 
    If Sheets("Consult").Range("F1") <> Empty Then
        NextRow = NextRow + 1
    End If
    
    If Target.Column = 13 Then
        If Target.Value = "Yes" Then
            Range("A" & Target.Row, Range("J" & Target.Row)).Copy _
                Destination:=Sheets("Consult").Range("F" & NextRow)
        End If
    End If
    
End Sub


Report •

#2
January 12, 2017 at 07:09:06
Thanks so much for this!!! So my next question, as I am very very very new to this, is how do I then assign this macro to the Row M? I've tried to figure out how to do it but am not sure

Thanks!!


Report •

#3
January 12, 2017 at 07:22:04
Ok, what you want to do is

1) Ensure you have at least two sheets in your workbook one of them must be called "Consult" and the other "Intake"

2) whilst in your workbook, press and hold the Alt key and tap F11 this will open the VBE (Visual Basic Editor).

3) Double click on the sheet called "Intake" within the project explorer of the VBE, then paste the code into the empty space

In terms of refering to column M

Where I have this line of code

If Target.Column = 13 Then

it will automically refer to column M of the sheet where the code in pasted as M is the 13th letter of the alphabet.

Now when you change column M of sheet intake to "Yes" (case sensitive) it will trigger the macro.

Hope this makes sense if not then please let us know

message edited by AlwaysWillingToLearn


Report •

Related Solutions

#4
January 16, 2017 at 02:04:12
Did you have any luck with this?

Report •

Ask Question