VBA Macro to Move complete Row to New Sheet in a table/Auto updating priority list in a table when new data entered

[VBA_Whiplash]

Hi all,

I have tried multiple codes of other threads to get this action to work. I am wanting two things if possible, the first is to move a row based on condition (Complete) in my CDM Builds sheet to another sheet named Completed Builds automatically as the conditions are selected/changed.

The other thing I am looking to do is auto update the CDM Builds sheet when the in order of stat, high, medium, low - appearing in that order, so when a new entry is input with that condition it will auto update into its correct grouping and alphabetize (A-Z) within that grouping based on trade name.

Appreciate the help

PS: There is a bit of conditional formatting in this sheet/data validation. I kept getting an error when trying to use L2BB to post the range of data.

 

[gd6noob]

You can try this

Sub copydata()

Dim count_col, count_row As Integer
Dim build, completed As Worksheet

Set build = ThisWorkbook.Sheets("CDM Build")
Set completed = ThisWorkbook.Sheets("Completed Buids")

count_col = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))

build.Activate

ActiveSheet.Range("A1").AutoFilter field:=1, Criteria1:="Complete"

build.Range(Cells(1, 1), Cells(count_row, count_col)).Offset(1).SpecialCells(xlCellTypeVisible).Copy  'copies only visible cells
completed.Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues 'finds next empty cell and paste

Application.CutCopyMode = xlCopy

Range("A1").CurrentRegion.Offset(1).EntireRow.Delete

Range("A1").Select
build.ShowAllData

Range("A1").CurrentRegion.Sort key1:=Range("A1"), order1:=xlDescending, Header:=xlYes  'sorts

End Sub