Move row to different worksheets based on dropdown selection

[afibdiva]

Hi, I'm so new to this and have only basic knowledge of Excel. However, i desperately need help with a VBA code.
I was tasked with an Appointment Tracker for a group of MDs - for return appointments in the future.
I have 8 MDs for which i made 8 specific worksheets.
Is there a way to automatically move a row from a MAIN worksheet to the specific MD worksheet based on a dropdown choice in Colum D (choices being the 8 MDs)??? And also delete that specific row that was just moved from the MAIN worksheet?
Also, is it possible to keep the same headers as the MAIN worksheet for each specific worksheet?

Thanks for whoever can help a technologically-challenged RN. It would help a lot if i can get this to work. THANKS.
AFibDiva

 

[jolivanes]

It's simple enough but:
The dropdown shows "War"
The selected cell in Column D has "Yeung"
The 2nd picture shows "Bal" sheet selected.
Do you want to
If you change a cell in Column D by selecting from a dropdown, clear all data of the sheet with that same name and copy the header and the just changed row to that sheet?

 

[afibdiva]

Thanks for responding. Sorry, I guess that was just Bec of how I did the screenshot.

MAIN Worksheet : Colum D (dropdown selection of 8 MDs which also correspond to the 8 different worksheets (Sheets 2-9)
MAIN Worksheet is sort of my Dashboard which I update multiple times a day.

I want to just work on MAIN Worksheet BUT if I choose an option from Dropdown List (Column D) - I'd like that row to move to the specific (same-name Worksheet).
If Blank, stay in MAIN worksheet... until I choose option from Column D.

At this point, I really don't care about the headers on the 8 Target Worksheets

I hope I'm making sense... I think I'm way over my head on this. But thank you very much for whatever info you can send me.

Desperate Nurse
AfibDiva

 

[jolivanes]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range
Set targ = Range("D1:D" & Cells(Rows.Count, 4).End(xlUp).Row)
If Not Intersect(Target, targ) Is Nothing Then
    ActiveCell.Offset(, -3).Resize(, 8).Copy Sheets(ActiveCell.Value).Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
End Sub

Put code in the module for the "Main" sheet.
Select from your dropdown and check result.

 

[afibdiva]

THANK YOU, THANK YOU, THANK YOU!!!
It worked like a charm.

Now, I only need to make it disappear from the MAIN worksheet when I choose an option from the dropdown menu.
Thanks a lot, Jolivanes!

 

[jolivanes]

AFAIK, you can't do that with the "Worksheet_Change" event.
Deleting cells in Column D will trigger the code again so the end result will be that all data from changed cell and below will be deleted.

 

[jolivanes]

Checked up on that a little more.
Delete previous and change to following.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range
Set targ = Range("D1:D" & Cells(Rows.Count, 4).End(xlUp).Row)
If Not Intersect(Target, targ) Is Nothing Then
    Application.EnableEvents = False
    With ActiveCell.Offset(, -3).Resize(, 8)
        .Copy Sheets(ActiveCell.Value).Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Delete Shift:=xlUp
    End With
    Application.EnableEvents = True
End If
End Sub
End Sub

Test on a copy of your original first.

 

[afibdiva]

That worked!!!! Thank you so very, very much!!!!

Because of COVID-19, we are so understaffed, overworked, and literally pulling our hairs out. We have been doing work that are beyond our scope of practice. Excel Spreadsheets to make Trackers like this being one of them. As frontline healthcare workers, we are trained to do stuff to live human beings but we barely know our way around Excel.

Your Code was PERFECT!!!! Thanks a million.

On behalf of the team, i sign...
From the grateful RNs and MDs of the Cardiology Department
Hospital XYZ, Central Valley CA
Apologies, but HIPAA confidentiality does not allow me to disclose details

 

[jolivanes]

Glad to have been of help.
You people deserve all the help you can get. I admire you.
Keep up the good work and good luck and stay safe to all.