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]

I have managed to do this but I get run time error when i insert copied cells on to the existing sheet, any suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 14 And UCase(Target) = "YES" Then
        Cells(Target.Row, Target.Column).EntireRow.Copy Destination:=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
        Application.EnableEvents = False
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
End Sub

 

[samunders]

Attached

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 14 Then Exit Sub
    If Target = "YES" Then
        Target.EntireRow.Copy Sheets("Archive").Cells(Sheets("Archive").Rows.Count, "A").End(xlUp).Offset(1)
        Application.EnableEvents = False
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
End Sub

 

[samunders]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 14 Then Exit Sub
    If Target = "YES" Then
        Target.EntireRow.Copy Sheets("Archive").Cells(Sheets("Archive").Rows.Count, "A").End(xlUp).Offset(1)
        Application.EnableEvents = False
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
End Sub

That have stopped the error with the macro from the Create > Active Worksheet. however now I'm trying to hit yes in Colum 14, its not copying, pasting to the archive sheet, then deleting the target row

 

[samunders]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column = 14 And UCase(Target) = "YES" Then
        Target.EntireRow.Copy Sheets("Archive").Cells(Sheets("Archive").Rows.Count, "A").End(xlUp).Offset(1)
        Application.EnableEvents = False
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
End Sub

This seems to have done the trick.


I now need to get the Cells D6 and onward in Colum D to auto sort with the newest at the top when the worksheet is open

 

[samunders]

Private Sub Workbook_Open()

    Sheet4.Range("D6:D99999").Sort key1:=Sheet2.Range("D6:N99999"), order1:=xlAscending, Header:=xlYes

End Sub

Would this work?

 

[mumps]

Try:

Private Sub Workbook_Open()
     Sheets(4).Cells(1, 4).Sort Key1:=Columns(4), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
End Sub

Make sure you place the macro in the code module for ThisWorkbook.

 

[samunders]

Still not working this is the VBA for the macro I just want it to run when i open the work book

Sub Sort_Archive()
'
' Sort_Archive Macro
'

'
    Range("D6:D17").Select
    ActiveWorkbook.Worksheets("Archive").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Archive").Sort.SortFields.Add Key:=Range("D6"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Archive").Sort
        .SetRange Range("D6:N99999")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[mumps]

Could you upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here (de-sensitized if necessary).