VBA to archive data with Specific Value & Macro to email when a button is clicked.

[samunders]

Hello Guys,

Firstly thank you for any help that comes my way, I am slowly picking up tips from this message board but I haven't quiet got my head around VBA.

I am building (trying to build) a handover sheet for active things of note, and when they are completed based on a yes value move that "active" hand over to the Archive sheet.

The first one is a macro to send a generic an email to 4 email addresses when a new submission is made, this is to prompt people to check the worksheet when something new is added.

The second is to copy and paste 1 row to the archive sheet and then delete 3 rows from the the active sheet, with the archives auto sorted to with most recent.

I hope this and the screen shots help

Thank you so much for any help offered.

 

[samunders]

Dropbox Link

Hopefully that work and thanks for all your help so far.

 

[samunders]

Handing Over Sheet.xlsx.xlsm

Hope this link works, also thanks for all your help

 

[mumps]

Try this version:

Private Sub Workbook_Open()
    Dim WS As Worksheet
    For Each WS In Sheets(Array("Active", "Archive"))
        With WS
            .Unprotect ""
            .Cells(5, 4).Sort Key1:=.Columns(4), Order1:=xlDescending, Orientation:=xlTopToBottom, Header:=xlYes
            .Protect ""
        End With
    Next WS
    Sheets("Active").Activate
End Sub

Keep in mind that if you have no data such as in the Active sheet, the macro will return an error.

 

[samunders]

@mumps I cannot thank you enough, I am so happy, it was starting to make me crazy! ha cheers
now I'm going to play with sending emails from excel thank you

 

[mumps]

You are very welcome.