Creating macros for Tracking system

[rbrasill]

Good Morning everyone. I know what I am about to ask has probably been asked a million times over, but I wanted to ask in a way that pertains to my situation. I hope you all understand. I have created a tracking system for my wife who is starting a small business from home. I am trying to make it as simple to use for her so she can make sure everything she is doing is being properly tracked. Below is a list of questions that I am trying to get help with. I am trying to create macros for these situations so that the tracking system is pretty self-sufficient. But to be honest, I am not savvy with writing macros in VBA, so I am reaching out for some help. I will gladly answer any questions you have to make it easier for everyone that is willing to help. I really appreciate any help that can be provided. Please see the below questions.

1. Move completed orders to completed orders tab when box is checked in column E. Remove that row from current orders tab and move all current orders up. Do not copy New Orders button to completed orders tab

2. Have new orders button auto populate next sequential number and fill in date received based on when button is pushed, while keeping all the same formatting at previous cells

3. Have date auto fill on completed orders tab when current order is complete (date complete button is clicked) and moved to completed orders tab (column G)

4. Have inventory be deducted based on completed order (I.e. we have 20 boxes, one order completed, now there are 19 boxes) while being able to add to inventory number without messing up previous orders

5. Be able to un-check complete box and have it move back to current orders tab in case something happens with that order. And put it back in sequential order.

 

[rbrasill]

Attached are pictures of how my tabs are set up for referece.

 

[rbrasill]

Below are the two macros I currently have. They sort of work, but are not fully what I want.

This is the code used to move to the completed tab, but it was originally set up to move based on a word and not checking a box. It also does not have the option to add the date it is moved on the completed orders tab. Also, it does not have the ability to bring the row back if I un-check the box. Not to mention, it automatically brings the New Order button to that tab as well, which I do not want.

Private Sub Worksheet_Change(ByVal Target As Range)

 Application.ScreenUpdating = False

 Application.EnableEvents = False

 If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub

 On Error GoTo errhandler

 If Target = "Complete" Then

 Target.EntireRow.Copy Sheets("Completed Orders").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

 Target.EntireRow.Delete

 End If

 Application.ScreenUpdating = True

errhandler:

 Application.EnableEvents = True

 Application.ScreenUpdating = True

End Sub

This is the code used for the new order command button. It works decent enough, but again, it creates the same button on the completed tab whenever something complete moves to the other sheet. Also, it does not automatically fill a date in column B when the button is clicked.

Sub Insert_New_Rows()

Dim Lr As Integer, Fr As Integer

Fr = Columns("A").Find(What:="Order #", After:=Range("A1")).Row 'Searching row of "Order #" header

Lr = Range("A" & Fr).End(xlDown).Row 'Searching last row in Risk table

 Rows(Lr + 1).Insert Shift:=xlDown 'Inserting new row

 Cells(Lr + 1, "A") = Cells(Lr, "A") + 1 'Adding a sequential number

 Rows(Lr).Copy 'Copying format of last row

 Rows(Lr + 1).PasteSpecial Paste:=xlPasteFormats 'Pasting format to new row

 Application.CutCopyMode = False 'Deactivating copy mode

 Cells(Lr + 1, "B").Select

End Sub