Copy row to another sheet depending on drop down list selection

EstimatorJJS · Jul 25th 2022

Hello,

I am trying to create a spread sheet that will allow for rows to be copied automatically and put into a separate sheet depending on the drop down selection.

i.e. : If "change order" is selected, I would like that row to be copied to a sheet titled Change order.

I cannot for the life of me make this work. Help?

Mumps · Jul 26th 2022

Where is the drop down containing "change order"? Does the Change Order sheet need to be created? I don't see it in your file. Please clarify in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data.

Daniel · Jul 26th 2022

Pessoal,

Desculpa perguntar aqui, mas rodei o forum inteiro e nao descobri como criar um tópico novo

Deve ser a pergunta mais idiota que vcs vem hoje, mas eu nao achei

Alguem pode me ajudar?

EstimatorJJS · Jul 27th 2022

The drop down list is on the "BIDS" sheet column "I".

The drop down list says "CO" or "PACKAGE".

If CO is selected I need the entire row to be copied and put on the change order sheet.

If package is selected I need the row to be copied to the "Package" Sheet.

Does that clarify enough? I am sorry I am new to the forum.

Mumps · Jul 27th 2022

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Bids sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column I.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 9 Then Exit Sub
    Dim lRow As Long
    Application.ScreenUpdating = False
    Select Case Target.Value
        Case "PACKAGE"
            With Sheets("Package")
                lRow = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                Range("B" & Target.Row).Resize(, 7).Copy .Range("B" & lRow + 1)
            End With
        Case "CO"
            With Sheets("Change Order")
                lRow = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                Range("B" & Target.Row).Resize(, 7).Copy .Range("B" & lRow + 1)
            End With
    End Select
    Application.ScreenUpdating = True
End Sub

Display More

EstimatorJJS · Jul 27th 2022

THANK YOU SO MUCH!!!!

EstimatorJJS · Jul 27th 2022

Now, one last thing.

If I want to add another drop down list to column J to do the same thing only to different sheets.

What portion of the code do I change to reflect that? Can you highlight were I should change the column? and where I should indicate how to copy to another sheet.

Example: I am now going to make a drop down list in column "J" that have the options : Not awarded, Awarded, Working, and Completed ready to invoice.

How do I add that in?

Thank you!

EstimatorJJS · Jul 27th 2022

I have added a new drop down in column J to do the same thing as before. Copy row to corrilating sheet.

How do I code this in as well? Please see previous comment for more details. Thank you!

Quote from Mumps

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Bids sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column I.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 9 Then Exit Sub
    Dim lRow As Long
    Application.ScreenUpdating = False
    Select Case Target.Value
        Case "PACKAGE"
            With Sheets("Package")
                lRow = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                Range("B" & Target.Row).Resize(, 7).Copy .Range("B" & lRow + 1)
            End With
        Case "CO"
            With Sheets("Change Order")
                lRow = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                Range("B" & Target.Row).Resize(, 7).Copy .Range("B" & lRow + 1)
            End With
    End Select
    Application.ScreenUpdating = True
End Sub

Display More

Mumps · Jul 27th 2022

Try:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("I:I,J:J")) Is Nothing Then Exit Sub
    Dim lRow As Long
    Application.ScreenUpdating = False
    Select Case Target.Column
        Case Is = 9
            Select Case Target.Value
                Case "PACKAGE"
                    With Sheets("Package")
                        lRow = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                        Range("B" & Target.Row).Resize(, 7).Copy .Range("B" & lRow + 1)
                    End With
                Case "CO"
                    With Sheets("Change Order")
                        lRow = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                        Range("B" & Target.Row).Resize(, 7).Copy .Range("B" & lRow + 1)
                    End With
            End Select
        Case Is = 10
            Select Case Target.Value
                Case "Awarded"
                    With Sheets("Awarded")
                        lRow = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                        Range("B" & Target.Row).Resize(, 7).Copy .Range("B" & lRow + 1)
                    End With
                Case "Not Awarded"
                    With Sheets("Not Awarded")
                        lRow = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                        Range("B" & Target.Row).Resize(, 7).Copy .Range("B" & lRow + 1)
                    End With
                Case "Working"
                    With Sheets("Working")
                        lRow = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                        Range("B" & Target.Row).Resize(, 7).Copy .Range("B" & lRow + 1)
                    End With
                Case "Completed"
                    With Sheets("Completed")
                        lRow = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                        Range("B" & Target.Row).Resize(, 7).Copy .Range("B" & lRow + 1)
                    End With
            End Select
    End Select
    Application.ScreenUpdating = True
End Sub

Display More

Please note that you have misspelled the sheet name "Not Awarded" in your file.

Copy row to another sheet depending on drop down list selection

EstimatorJJS Jul 27th 2022

EstimatorJJS Jul 27th 2022

EstimatorJJS Jul 27th 2022

Participate now!

Share

Similar Threads

ProductSearch - Unhiding/Hiding Rows on Multiple Worksheets based on Column Value

Move certain data from one column to another sheet based on selection in drop down selection

Use VBA to Copy & Paste Unique Value from One Worksheet to Another (Paste as Values)