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

BrandyJ

New Member
Joined
Jul 5, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?

1688603192978.png
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
If I understand your requirements correctly, please try the following code on a copy of your workbook.
VBA Code:
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
 
Upvote 0
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.
VBA Code:
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 :)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

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
Back
Top