Solved Cut and paste rows of cells depending on drop down list

Microsoft Excel 2010 - complete product...
April 9, 2015 at 08:36:04
Specs: Windows 7
So here is the deal, I am not familiar with VBA and I was trying to create a macro to complete this task and have failed miserably. I work for a health insurance company and I have to maintain a roster of physicians for each hospital or organization. Each workbook has 3 worksheets. Sheet 1 is titled "Roster" Sheet 2 is titled "Terms" and Sheet 3 is titled "Adds". For the "Roster" worksheet, In column A I want to have a drop down to select either "Active" or "Termed" according to whether that particular physician is currently on this roster or if they have "termed" and been removed from the roster. If I choose the drop down to say "termed" I want the entire row to be cut from workbook "roster" and pasted in the next available row in the "termed" worksheet. Is there an easy way to do this?

See More: Cut and paste rows of cells depending on drop down list

Report •

April 9, 2015 at 17:06:02
✔ Best Answer
You are inconsistent in your use of certain words, so I making some assumptions:

First you say:

For the "Roster" worksheet...

then you say:

I want the entire row to be cut from workbook "roster"

I assume you mean the Roster worksheet.

Another time you say:

Sheet 2 is titled "Terms"

Then you say:

... in the "termed" worksheet

I assume you mean the Terms worksheet.

Assuming those assumptions are correct, this code should should accomplish your goal.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to a single cell in Column A
 If Target.Column = 1 And Target.Cells.Count = 1 Then
'Determine if Termed was chosen
  If Target = "Termed" Then
'If Yes...
''Disable Events
    Application.EnableEvents = False
''Determine next empty Row on Sheet Terms
     nxtRw = Sheets("Terms").Range("A" & Rows.Count).End(xlUp).Row + 1
''Copy, Paste then Delete Row
      Rows(Target.Row).EntireRow.Copy _
       Destination:=Sheets("Terms").Range("A" & nxtRw)
''Re-enable Events
    Application.EnableEvents = True
   End If
  End If
End Sub

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

Report •
Related Solutions

Ask Question