Macro for copying to new worksheet then clear contents

[M3L333]

I am a novice excel user. I need a macro that I will assign to a command button. When the word "CLOSED" appears in column M, I would like the command button to copy the information from columns B-M into a new worksheet titled "discharges". I would like to clear contents from the original worksheet. I would need the macro to recognize the next row to insert the data onto, starting with row 2. The information can be inserted into columns B-M on the new worksheet.

 

[Joe4]

Are you just wanting to copy the information from the row with "CLOSED" in column M, or the whole sheet?
How is column "M" being populated with the word "CLOSED"?
If it is being manually entered or selected, you can have the macro run automatically without you having to do anything to start it, if that is preferrable.
Also, when clearing the contents of the row(s), do you want to leave the row blank, or delete it so that all information below it moves up to fill in the gaps?

 

[M3L333]

I would like to copy the information from the row with the word "CLOSED" in column M. I am generating the word closed with data validation, I have a list that has this option. Once the information is up to date with all the "CLOSED" files the worker will click the command button, which would move the information from column B-M in worksheet1 (bed registry) to the same columns in worksheet 2 (discharges). when clearing the contents of the row I want it to appear blank in only columns B-M (leaving the information in column A). I do not want the row to move up. Thanks

 

[Joe4]

Try this code:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    Dim nr As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column M with data on Sheet 1
    lr = Sheets("Sheet1").Cells(Rows.Count, "M").End(xlUp).Row
    
'   Loop through all rows on sheet 1 and check column M for closed
    For r = 2 To lr
        If Sheets("Sheet1").Cells(r, "M") = "CLOSED" Then
'           Find next row to paste to on Sheet 2
            nr = Sheets("Sheet2").Cells(Rows.Count, "M").End(xlUp).Row + 1
'           Copy columns B-M to Sheet 2
            Sheets("Sheet1").Range(Cells(r, "B"), Cells(r, "M")).Copy Sheets("Sheet2").Cells(nr, "B")
'           Clear columns B-M on Sheet 1
            Sheets("Sheet1").Range(Cells(r, "B"), Cells(r, "M")).ClearContents
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[M3L333]

This works like a charm! Thank you so much. I really appreciate the code. This is very helpful to my work.

 

[Joe4]

You are welcome.
Glad I was able to help!