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]

Okay, Thank you! Am I supposed to substitute any codes you provided to actual sheets? Sorry, I am just not sure how.

 

[Michael M]

Am I supposed to substitute any codes you provided to actual sheets?

Sorry, I don't understand this request.....The code will go in the MasterSheet module

 

[jdlerry]

Really not working for me. My bad. This helped me How to move entire row to another sheet based on cell value in Excel?
but I guess I don't know how to add the following in the Status.

Completed: CCG Response

Completed: GSS Notification

Completed: IV Notification

 

[jdlerry]

Am I supposed to substitute any codes you provided to actual sheets?

Sorry, I don't understand this request.....The code will go in the MasterSheet module

That's what I did here but not working.

 

[Michael M]

During this thread you haven't stated that you wanted the row copied to another sheet....You simply staed you want to go to the Complted Sheet.....Whole different story
Us this instead, In the Master Sheet module

Private Sub worksheet_selectionChange(ByVal target As Range)
Dim lr As Long
If Not Intersect(target, Range("K:K")) Is Nothing Then
ans = Evaluate("Left(" & ActiveCell.Address & ",9)")
 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]

During this thread you haven't stated that you wanted the row copied to another sheet....You simply staed you want to go to the Complted Sheet.....Whole different story
Us this instead, In the Master Sheet module

Private Sub worksheet_selectionChange(ByVal target As Range)
Dim lr As Long
If Not Intersect(target, Range("K:K")) Is Nothing Then
ans = Evaluate("Left(" & ActiveCell.Address & ",9)")
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

I really really appreciate all your help but I think it's not working. Thank you!

 

[Michael M]

Not working doesn't help me....Is there an error ?
At some point did you quir rhe code before it had completed ?
If so, you will probably find that Enableevents is still turned off....If so, In the VBA window press CTRL + G
In the window that appears, paste this line

Application.EnableEvents = True

Then press Enter, try the worksheet again

 

[Michael M]

Also try it this way

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)")
 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]

Also try it this way

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)")
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

I think my challenge here is understanding how this whole thing works. I know it is too much but I need a specific instruction please. So I pasted the code in module and then what? Should I click anything (like F5?) after that or it will do its magic? It does not show me any error.

 

[Michael M]

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