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

Roadknight87

New Member
Joined
Jul 14, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Code for Spreadsheet.jpg
    Code for Spreadsheet.jpg
    253.7 KB · Views: 21

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,163
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Roadknight87

New Member
Joined
Jul 14, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,163
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:-

VBA Code:
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

New Member
Joined
Jul 14, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,163

ADVERTISEMENT

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:-

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

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

End Sub

Cheerio,
vcoolio.
 

Roadknight87

New Member
Joined
Jul 14, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,163
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:-

VBA Code:
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.
 

Forum statistics

Threads
1,140,930
Messages
5,703,228
Members
421,285
Latest member
Bebek

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top