Moving rows to different sheets

[Cstewart29]

Hi,

So I have been trying to build a spreadsheet that requires moving rows of information from the main "Caseload" sheet into any one of 12 sheets (January-December) in the same document, also deleting the information from the "caseload" sheet.
The idea would be to highlight the row and click a button that will move the row. I have decided to add to column R a drop down list 1-12 to use to determine which month to transfer to.

Now while I have created a button to move a row to ONE other sheet before, this task has proven too much so far so I was hoping to ask some smart person for help?

Thanks to anyone who can help. Let me know if there is anything I haven't made clear!

 

[Kayfabe]

Hello! Did you ever figure this out? Could you share if so?

Thank you!

 

[Cstewart29]

Hello! Did you ever figure this out? Could you share if so?

Thank you!

Hi,

No I haven't sorry, going to keep looking though!

 

[kevin9999]

Try the following on a copy of your data. Assumptions (in the absence of any data sample provided) include that the dropdown list consists of the months "January, February, March..."etc.; and that each sheet (other than the Caseload sheet) is named exactly as the various month names in the validation list; that you want the row data from column A-Q inclusive moved (not column R); and that each month sheet has a header row in row 1. If any of these assumptions are wrong, please advise otherwise.

Option Explicit
Sub Move_Row()
    On Error GoTo Escape
    Application.EnableEvents = False
    
    Dim ws As String, rng As Range
    ws = Intersect(ActiveCell.EntireRow, Columns(18)).Value2
    
    If Selection.Cells.CountLarge > 1 Then
        MsgBox "Select one row only"
        Exit Sub
    End If
    
    If ws = "" Then
        MsgBox "No Month Selected in column R"
        Exit Sub
    End If
    
    If MsgBox("You're about to move Row" & ActiveCell.Row & " to sheet " & ws & vbNewLine _
    & "Do you wish to proceed?", vbYesNo, "Confirm") = vbNo Then
        Exit Sub
    End If
    
    Set rng = Intersect(ActiveCell.EntireRow, Range("A:Q"))
    With rng
        .Copy Worksheets(ws).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        .EntireRow.Delete
    End With
    
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[Cstewart29]

Try the following on a copy of your data. Assumptions (in the absence of any data sample provided) include that the dropdown list consists of the months "January, February, March..."etc.; and that each sheet (other than the Caseload sheet) is named exactly as the various month names in the validation list; that you want the row data from column A-Q inclusive moved (not column R); and that each month sheet has a header row in row 1. If any of these assumptions are wrong, please advise otherwise.

Option Explicit
Sub Move_Row()
    On Error GoTo Escape
    Application.EnableEvents = False
   
    Dim ws As String, rng As Range
    ws = Intersect(ActiveCell.EntireRow, Columns(18)).Value2
   
    If Selection.Cells.CountLarge > 1 Then
        MsgBox "Select one row only"
        Exit Sub
    End If
   
    If ws = "" Then
        MsgBox "No Month Selected in column R"
        Exit Sub
    End If
   
    If MsgBox("You're about to move Row" & ActiveCell.Row & " to sheet " & ws & vbNewLine _
    & "Do you wish to proceed?", vbYesNo, "Confirm") = vbNo Then
        Exit Sub
    End If
   
    Set rng = Intersect(ActiveCell.EntireRow, Range("A:Q"))
    With rng
        .Copy Worksheets(ws).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        .EntireRow.Delete
    End With
   
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Hi,

This worked very well. Thanks so much its great! Makes the sheet work so much better.

 

[Cstewart29]

Hello! Did you ever figure this out? Could you share if so?

Thank you!

The solution from Kevin9999 on this thread worked well.

 

[kevin9999]

Hi,

This worked very well. Thanks so much its great! Makes the sheet work so much better.

Happy to help, and thanks for the feedback

 

[Kayfabe]

Try the following on a copy of your data. Assumptions (in the absence of any data sample provided) include that the dropdown list consists of the months "January, February, March..."etc.; and that each sheet (other than the Caseload sheet) is named exactly as the various month names in the validation list; that you want the row data from column A-Q inclusive moved (not column R); and that each month sheet has a header row in row 1. If any of these assumptions are wrong, please advise otherwise.

Option Explicit
Sub Move_Row()
    On Error GoTo Escape
    Application.EnableEvents = False
   
    Dim ws As String, rng As Range
    ws = Intersect(ActiveCell.EntireRow, Columns(18)).Value2
   
    If Selection.Cells.CountLarge > 1 Then
        MsgBox "Select one row only"
        Exit Sub
    End If
   
    If ws = "" Then
        MsgBox "No Month Selected in column R"
        Exit Sub
    End If
   
    If MsgBox("You're about to move Row" & ActiveCell.Row & " to sheet " & ws & vbNewLine _
    & "Do you wish to proceed?", vbYesNo, "Confirm") = vbNo Then
        Exit Sub
    End If
   
    Set rng = Intersect(ActiveCell.EntireRow, Range("A:Q"))
    With rng
        .Copy Worksheets(ws).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        .EntireRow.Delete
    End With
   
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Thank you!