VBA Code Request - Cut One Row and Paste on new line of Another Worksheet

[BrandyJ]

Good evening all,

I am new to coding and have been trying to teach myself how to do this, but I think I have rammed my head against a wall for long enough...

So, here is what I would like the macro to do:

I have a spreadsheet with individual case info, upon closing this case I would like to have a Pop-up box confirm you DO want to close this case. If yes, then cut entire row from one worksheet of a workbook, and paste the info onto another worksheet in the same workbook, on the next line as this workbook will compile the info as case close. Can you help with all the syntax needed, please?

 

[jolivanes]

Re: "I have a spreadsheet with individual case info"
Would that translate into: "I have a Worksheet with data"?
Re: "upon closing this case"
Explain what that means. Do you mean exiting or closing the Workbook?
Re: "I would like to have a Pop-up box confirm you DO want to close this case."
If previously you meant to close the workbook, do you want a message box with a Yes and No as choice?
Re: "If yes, then cut entire row from one worksheet of a workbook". If No, don't save the workbook on exit? Or maybe do save it?
Entire row meaning row 654?
from one worksheet of a workbook meaning, just pick any sheet of whichever workbook you want?
Re: "paste the info onto another worksheet in the same workbook"
Just whichever sheet catches my fancy?
Re: "on the next line"
This shoulbe be easy. I assume the next empty line measured from Column ABC?
Re: "as this workbook will compile the info as case close"
Translate please.

You probably don't like all these questions but I only do it for you to realize that the info needs to be there in order for someone to help you.
If possible at all, be short and to the point, concise I guess, but don't leave anything out.

 

[kevin9999]

If I understand your requirements correctly, please try the following code on a copy of your workbook.

Option Explicit '<~~ Always add this - ensures declaration of variables used
Sub Close_Case()
    'Declare and set sheet Variables
    Dim ws1 As Worksheet, ws2 As Worksheet, s As String
    Set ws1 = Worksheets("Active")
    Set ws2 = Worksheets("Closed")
    
    'Ensure only one case selected
    With ws1.Cells(1, 1).CurrentRegion
        If .SpecialCells(xlCellTypeVisible).Rows.Count > 2 Then
            MsgBox "Select one case only to close"
            Exit Sub
        Else
        'Ask whether to proceed - if yes, then copy and delete case row
            s = MsgBox("Do you want to close this case?", vbYesNo)
            If s = vbYes Then
                .Offset(1).Resize(.Rows.Count - 1).Copy ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
                .Offset(1).EntireRow.Delete
                With ws1
                    .ShowAllData
                End With
            End If
        End If
    End With
End Sub

 

[BrandyJ]

Re: "I have a spreadsheet with individual case info"
Would that translate into: "I have a Worksheet with data"?
Re: "upon closing this case"
Explain what that means. Do you mean exiting or closing the Workbook?
Re: "I would like to have a Pop-up box confirm you DO want to close this case."
If previously you meant to close the workbook, do you want a message box with a Yes and No as choice?
Re: "If yes, then cut entire row from one worksheet of a workbook". If No, don't save the workbook on exit? Or maybe do save it?
Entire row meaning row 654?
from one worksheet of a workbook meaning, just pick any sheet of whichever workbook you want?
Re: "paste the info onto another worksheet in the same workbook"
Just whichever sheet catches my fancy?
Re: "on the next line"
This shoulbe be easy. I assume the next empty line measured from Column ABC?
Re: "as this workbook will compile the info as case close"
Translate please.

You probably don't like all these questions but I only do it for you to realize that the info needs to be there in order for someone to help you.
If possible at all, be short and to the point, concise I guess, but don't leave anything out.

this worksheet if for work. This will be an inventory workbook. close case is an internal action, not really relevant to the function of the workbook, it would just be the trigger that would initiate the action.
the

If I understand your requirements correctly, please try the following code on a copy of your workbook.

Option Explicit '<~~ Always add this - ensures declaration of variables used
Sub Close_Case()
    'Declare and set sheet Variables
    Dim ws1 As Worksheet, ws2 As Worksheet, s As String
    Set ws1 = Worksheets("Active")
    Set ws2 = Worksheets("Closed")
   
    'Ensure only one case selected
    With ws1.Cells(1, 1).CurrentRegion
        If .SpecialCells(xlCellTypeVisible).Rows.Count > 2 Then
            MsgBox "Select one case only to close"
            Exit Sub
        Else
        'Ask whether to proceed - if yes, then copy and delete case row
            s = MsgBox("Do you want to close this case?", vbYesNo)
            If s = vbYes Then
                .Offset(1).Resize(.Rows.Count - 1).Copy ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
                .Offset(1).EntireRow.Delete
                With ws1
                    .ShowAllData
                End With
            End If
        End If
    End With
End Sub

Thank you so much. I will try this today and let you know if it worked

 

[BrandyJ]

this worksheet if for work. This will be an inventory workbook. close case is an internal action, not really relevant to the function of the workbook, it would just be the trigger that would initiate the action.
the

Thank you so much. I will try this today and let you know if it worked

Re: "I have a spreadsheet with individual case info"
Would that translate into: "I have a Worksheet with data"?
Re: "upon closing this case"
Explain what that means. Do you mean exiting or closing the Workbook?
Re: "I would like to have a Pop-up box confirm you DO want to close this case."
If previously you meant to close the workbook, do you want a message box with a Yes and No as choice?
Re: "If yes, then cut entire row from one worksheet of a workbook". If No, don't save the workbook on exit? Or maybe do save it?
Entire row meaning row 654?
from one worksheet of a workbook meaning, just pick any sheet of whichever workbook you want?
Re: "paste the info onto another worksheet in the same workbook"
Just whichever sheet catches my fancy?
Re: "on the next line"
This shoulbe be easy. I assume the next empty line measured from Column ABC?
Re: "as this workbook will compile the info as case close"
Translate please.

You probably don't like all these questions but I only do it for you to realize that the info needs to be there in order for someone to help you.
If possible at all, be short and to the point, concise I guess, but don't leave anything out.

the spreadsheet would have individual case information (data) for each case assigned to me
I would just like a message box to confirm you indeed want to cut this line of data out of this worksheet, essentially, and paste it another worksheet
If, yes, cut whichever row highlighted, if no, saving the workbook would be a good alternative
the line of data is coming from worksheet named "Active" and if closed, be cut and pasted on next line of the worksheet name "Closed" within the same workbook
yes, the next empty line of the "Closed" worksheet
Theoretically, as one closes cases out of the "Active" to "Closed" worksheets, "Active" decreases as "Closed" increases in the number of line of case data
thanks for considering all the details, it's appreciated