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 RN,
Pretty much so. However, I don't know what your criteria are in the drop downs so if the criteria are worksheet names then we could alter the code a little to bypass the "if target.value=" line.
Please note that I'm currently working away from home and doing this sort of thing is difficult on a phone so I won't be able to look at it for you until the weekend. In the meantime, try it as per your last post and see what happens.

Cheerio,
vcoolio.

 

[Roadknight87]

Hello RN,
Pretty much so. However, I don't know what your criteria are in the drop downs so if the criteria are worksheet names then we could alter the code a little to bypass the "if target.value=" line.
Please note that I'm currently working away from home and doing this sort of thing is difficult on a phone so I won't be able to look at it for you until the weekend. In the meantime, try it as per your last post and see what happens.

Cheerio,
vcoolio.

Hi vcoolio,

I used the code and it is working and yes my criteria in the drop down boxes is work sheet names as follows:

AB1
AB2
AC1
AC2
P01
P02
P03
P04

I noticed when the data transferred it took all 3 lines as 35 may have to go to a different sheet then 36 example below:
Row 35 - AB1
Row 36 - AB2

As this sheet is a running history of machine issues I need the information to populate on the next row. I have modified the code to start at the 2nd row.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B35:B37")) Is Nothing Then Exit Sub '---->Locks the criteria selection to three cells only.
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

If Target.Value = (("AB1")) Then '---->Place your required criteria here from the drop down.
Range("B35:N37").Copy Sheets("AB1").[B2]
End If

Application.ScreenUpdating = True

End Sub

I also added in a code to the work sheets to generate the date the information was put in.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 2 Then
Range("A" & Target.Row) = Date
Else
End If


End Sub

 

[vcoolio]

Hello RK,

Does this then mean, from your last post, that you've sorted it all out to suit?

Since the values in your drop downs are worksheet names, you can alter the code as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B35:B37")) Is Nothing Then Exit Sub 
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

Range("B35:N37").Copy Sheets(Target.Value).[B2]

Application.ScreenUpdating = True

End Sub

The data will go directly to the desired worksheet on selection of the worksheet name from the drop downs without using the 'IF' statement.

Cheerio,
vcoolio.

 

[Roadknight87]

Hi vcoolio,

I'm trying to make the date auto populate in certain columns using the following code

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
Range("N" & Target.Row) = Date

Else
End If

End Sub

But I am having trouble because when the data transfers from the original sheet the date doesn't generate, Do I have to add in a screen.update rule?

Kind Regards

Roadknight87

 

[vcoolio]

Hello RK,

Your code in post #24 works in a test I did. I'm assuming that once you enter a value in Column A you'd like the corresponding cell in Column N to populate with the current date. If I've missed something here then please let me know.

If its still not working for you, then try it as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

        If Target.Column = 1 Then
             Target.Offset(, 13) = Date
        End If

End Sub

Cheerio,
vcoolio.

 

[Roadknight87]

So the issue I am having is that, The data is transferring from one sheet to another via a macro, So I am not entering it but the code is transferring the data after I select the sheet it goes too. Then the date doesn't auto populate because I haven't typed anything into the cells...

 

[vcoolio]

Hello RK,

Seems like you need to use a Worksheet_Activate() event code placed in the destination sheet module:-

If the code you are using is copy/pasting the data to the destination sheet at say, A2, then try this:-

Private Sub Worksheet_Activate()

        Dim c As Range

        For Each c In Range("A2:A4") '----> Change the range to suit. This is just an example.
                c.Offset(, 13) = Date  '---->Date should populate in the corresponding cell(s)in Column N.
        Next c

End Sub

Once you open the destination sheet, the code will populate the date.

Cheerio,
vcoolio.