Need to move entire row based on cell drop down box value

[Roadknight87]

Hi I am very new to this so apologies, I am trying to move an entire Row from one sheet to another based on a choice from a drop down box. I have values from "A" to "M" and my drop down box is in "H" I had a code that was working for me but when I tried to add more it stopped working and then when I tried to start again it stopped working. "IMS 2021" is the main sheet and I want the information to stay here but also move to the relevant sheet based on the choice in "H". I also want to have a script in each of the other spreadsheets that when "H" is changed to completed it moves it to the "completed" sheet and deletes it from where it came from.

Private Sub Worksheet_Change(ByVal Target As Range)

' Check to see only one cell updated
If Target.CountLarge > 1 Then Exit Sub

' Check to see if entry is made in column H after row 1 and is set to "Active"
If Target.Column = 8 And Target.Row > 1 And Target.Value = "Active" Then
Application.EnableEvents = False
' Copy columns A to M to complete sheet in next available row
Range(Cells(Target.Row, "A"), Cells(Target.Row, "M")).Copy Sheets("Active Ideas").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Application.EnableEvents = True
End If

End Sub


Move to "completed" Sheet

Private Sub Worksheet_Change(ByVal Target As Range)

' Check to see only one cell updated
If Target.CountLarge > 1 Then Exit Sub

' Check to see if entry is made in column H after row 1 and is set to "Completed"
If Target.Column = 8 And Target.Row > 1 And Target.Value = "Completed" Then
Application.EnableEvents = False
' Copy columns A to M to complete sheet in next available row
Range(Cells(Target.Row, "A"), Cells(Target.Row, "M")).Copy Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
' Delete current row after copied
Rows(Target.Row).Delete
Application.EnableEvents = True
End If

End Sub

 

[vcoolio]

Hello Roadknight,

Do we assume that you have a number of worksheets (7?) which you require data transferred to dependent on your drop down selection?
For example: If "Capex" is selected the data goes to sheet "Capex". If "Active" is selected, the data goes to sheet "Active Ideas" etc.
If so, can you please supply the names of all the destination sheets as your criteria selections don't appear to match the worksheet names.

Cheerio,
vcoolio.

 

[Roadknight87]

Yes that is right, Active = Active Ideas, Ideas Bank = Ideas Bank, Capex = Capex, No Go = No-Go and Started - Not on board = Started not on board

 

[vcoolio]

Hello Roadknight,

Firstly, if there are any merged cells in your IMS 2021 input sheet, then un-merge them and re-format your data set without merged cells.
Next, place the following code into the IMS 2021 worksheet module:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns("H")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False
        
        x = Target.Row
        Range(Cells(x, "A"), Cells(x, "M")).Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)
        Sheets(Target.Value).Columns.AutoFit

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

and this code into the Workbook_SheetChange module:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Sh.Columns("H")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False
        
        x = Target.Row
                If Target.Value = "Completed" Then
                        Range(Cells(x, "A"), Cells(x, "M")).Copy Sheets("Completed").Range("A" & Rows.Count).End(3)(2)
                        Sh.Range(Cells(x, "A"), Cells(x, "M")).EntireRow.Delete
                End If
        Sheets("Completed").Columns.AutoFit

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

The first event code will transfer the relevant rows of data to the respective sheet on selection of a drop down value in Column H.
The second event code will transfer any row of data from any worksheet where "Completed" is selected from the drop downs in Column H and then delete the row from the source sheet.

I've attached a mock-up workbook here so that you can see how it works.

Please note that in the smock-up, I've adjusted the drop down criteria and worksheet names to match. This is the for the sake of good practice and uniformity which in turn simplifies the whole process. I recommend that you do the same in your actual workbook.
Please also test the codes in a sample of your modified workbook.

I hope that this helps.

Cheerio,
vcoolio.

 

[Roadknight87]

Thanks for your help but I have tried to do what you ask but nothing seems to be happening. I have inserted a module and copied the first code over and then tested with nothing moving and then I created another module with the 2nd code and still nothing. I don't have any merged cells and I have changed the names of the sheets to match the drop down boxes.

Kind Regards

Adam

 

[vcoolio]

Hello Adam,

I'm assuming that you have tried the sample that I supplied and seen that it works just fine. However, based on your last post, it would appear that you have placed the codes into the wrong modules. They are event codes and do not need to be assigned to a button.

To implement the first code from post #4:-
- Right click on the IMS 2021 worksheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

To implement the second code in post #4:-
- In the VB Editor, double click on 'ThisWorkbook'.
- Again, in the big white code field to the right, paste the above code.

Create a copy of your actual workbook and test the codes in that first.

Cheerio,
vcoolio.

 

[Roadknight87]

Thanks vcoolio, I did that and its all working! thanks for your help much appreciated.

Kind Regards

Adam

 

[vcoolio]

Hello RK,

You're welcome. Good to know that you've sorted it out.

Cheerio,
vcoolio.

 

[Roadknight87]

This isn't a big issue but am I able to run this code on a web based version of excel?

 

[vcoolio]

Hello RK,

I'm pretty sure that you can't. Just as in cloud stored files, macros are not supported. The file needs to be downloaded to to your desktop.

Cheerio,
vcoolio.