VBA

[jdlerry]

Hello everyone, I need your help please. I just need VBA code to get my reports working. What I want to do is whenever these dropdowns are selected, it should automatically go to another sheet.

Completed: CCG Response

Completed: GSS Notification

Completed: IV Notification

Another VBA code that I need help of is whenever a dropdown is selected, it should go to the intended sheet. For example, I have a masterlist in sheet 1 which contains projects and from the dropdowns, I will select the name of the owner per project. Once selected, it should automatically move to the intended sheet. The difference with the first concern is the destination sheet. There will be multiple destination sheets which correspond the owner.

Thank you!

 

[jdlerry]

No....if the code is pasted in the "MasterList" sheet module
THEN
if you make a change to any dropdown in column "K", that row should be pasted over to the"Completed Cases" Sheet on the lastrow + 1
If you are having further issues can you upload the workbook to DropBox, or similar hosting site, then post the link back here, so we can take a look at the problem

Thank you so much for the extra mile. When you check "K" which is the status, the 3 dropdowns that says Completed should transfer to Completed Cases once selected.

Referral-Tracker.xlsx

Shared with Dropbox

www.dropbox.com

 

[Michael M]

First thing...You MUST save the workbook as a macro enabled workbook....that is a file with a .xlsm file extension..NOT a .xlsx file extension.
If you don't, every time you save the file the macro will be lost
The code is pasted in the "MasterList" sheet module

 

[Michael M]

I've also made a slight change to make sure the other "Options" aren't copied across

Private Sub worksheet_Change(ByVal target As Range)
Dim lr As Long
If Not Intersect(target, Range("K:K")) Is Nothing Then
ans = Evaluate("Left(" & ActiveCell.Address & ",9)")
If ans <> "Completed" Then Exit Sub
 lr = Sheets("Completed Cases").Cells(Rows.Count, "A").End(xlUp).Row
Application.EnableEvents = False
Rows(ActiveCell.Row).Copy Sheets(ans & " Cases").Range("A" & lr + 1)
End If
Application.EnableEvents = True
End Sub

 

[jdlerry]

You are so amazing! it works now!!!!! One last concern. I probably have not mentioned this, but it's not just copy paste. It should be removed from the masterlist once tagged as completed. How do I that?

I've also made a slight change to make sure the other "Options" aren't copied across

Private Sub worksheet_Change(ByVal target As Range)
Dim lr As Long
If Not Intersect(target, Range("K:K")) Is Nothing Then
ans = Evaluate("Left(" & ActiveCell.Address & ",9)")
If ans <> "Completed" Then Exit Sub
lr = Sheets("Completed Cases").Cells(Rows.Count, "A").End(xlUp).Row
Application.EnableEvents = False
Rows(ActiveCell.Row).Copy Sheets(ans & " Cases").Range("A" & lr + 1)
End If
Application.EnableEvents = True
End Sub

 

[Michael M]

Here you go....replace the code in the Sheet module with this one

Private Sub worksheet_Change(ByVal target As Range)
Dim lr As Long
If Not Intersect(target, Range("K:K")) Is Nothing Then
ans = Evaluate("Left(" & ActiveCell.Address & ",9)")
If ans <> "Completed" Then Exit Sub
 lr = Sheets("Completed Cases").Cells(Rows.Count, "A").End(xlUp).Row
Application.EnableEvents = False
Rows(ActiveCell.Row).Copy Sheets(ans & " Cases").Range("A" & lr + 1)
Rows(ActiveCell.Row).Delete
End If
Application.EnableEvents = True
End Sub

 

[jdlerry]

Here you go....replace the code in the Sheet module with this one

Private Sub worksheet_Change(ByVal target As Range)
Dim lr As Long
If Not Intersect(target, Range("K:K")) Is Nothing Then
ans = Evaluate("Left(" & ActiveCell.Address & ",9)")
If ans <> "Completed" Then Exit Sub
lr = Sheets("Completed Cases").Cells(Rows.Count, "A").End(xlUp).Row
Application.EnableEvents = False
Rows(ActiveCell.Row).Copy Sheets(ans & " Cases").Range("A" & lr + 1)
Rows(ActiveCell.Row).Delete
End If
Application.EnableEvents = True
End Sub

Oh my!! It works!!!!! Thank you.

Not perfect though When I click save, exit and reopen, it does not work again. I checked and the code is still there. Not sure which step did I miss but here's the step by step process:
1. Saved as .xlsm
2. From Masterlist tab, I right clicked, then View Code.3
3. Paste the code you provided
4. ctrl +S to save
5. It worked!
6. Clicked ctrl +S to save and made sure it is xlsm then exit
7. reopened the file
8. Not working again

Really really appreciate your patience.

 

[Michael M]

Are you making a change to a cell in column "K" when you reopen the file ?
AND
The cell your are changing does have "Completed" as the first word in the text string ?

 

[jdlerry]

Are you making a change to a cell in column "K" when you reopen the file ?
AND
The cell your are changing does have "Completed" as the first word in the text string ?

Yes. So when I reopen to check if it still works, I change a cell in K with a dropdown that says Complete.

Are you making a change to a cell in column "K" when you reopen the file ?
AND
The cell your are changing does have "Completed" as the first word in the text string ?

It works now. THANK YOU!!Is there a way that this will work through Microsoft Teams as a shared file? or Macros don't work in web based?

 

[Michael M]

Sorry..i have no idea obout that.
Hopefully someone else may be able to help