Solved How To Move Rows From One Worksheet To Others Automatically?

March 14, 2018 at 15:59:47
Specs: Macintosh
Hi,
Can anyone help me write a Marco code that will move a row in excel to one of 3 other worksheets if "Yes" is entered into column "I"?
I have 4 worksheets, "Current", "Completed London", "Completed Edinburgh" and "Completed Manchester". I want a row to automatically move from the current list to one of the completed worksheets when I select "Yes" from a drop down list in column "I". The rows remaining are to move up so there are no empty rows.
All sheets are identical. Row 1 has my headings.
Columns A - J are in use holding various info. Its only column I that was to be the trigger.
Any response will be appreciated.

See More: How To Move Rows From One Worksheet To Others Automatically?

Report •

#1
March 20, 2018 at 15:23:33
How will the code know which sheet to move the row too? Having code that will be triggered by an event is fairly simple, but it needs to be told what to do once it's been triggered.

For example...

"If Yes is chosen in Column I, then go look in Column D and move the row to the sheet named in that column."

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


Report •

#2
March 21, 2018 at 01:35:05
Good point, you're right -that's why I've posted my query as I know absolutely nothing at all about macros.

So, would anyone be able to help with a macro for the above query and where "Yes" is chosen in Column I, then go look in Column D and move the row to the sheet named in that column?

Many thanks


Report •

#3
March 22, 2018 at 11:49:45
✔ Best Answer
Try this...

Right click the sheet tab for the "Current" sheet and choose View Code.
Paste the following code into the pane that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dstSht As Range
Dim nxtRw As Long

'Determine if Yes was chosen in Column 9 (I)
  If Target.Column = 9 Then
    If Target = "Yes" Then
    
'Determine destination sheet name using Column D
     dstSht = Range("D" & Target.Row)
     
'Determine next empty Row in Destination sheet
     nxtRw = Sheets(dstSht).Range("A" & Rows.Count).End(xlUp).Row + 1
    
'Copy/Paste
       Rows(Target.Row).EntireRow.Copy _
         Sheets(dstSht).Range("A" & nxtRw)
        
    End If
  End If
End Sub

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


Report •
Related Solutions


Ask Question