Creating macros for Tracking system

rbrasill

New Member
Joined
Apr 15, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

rbrasill

New Member
Joined
Apr 15, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Attached are pictures of how my tabs are set up for referece.
 

Attachments

  • Slide1.JPG
    Slide1.JPG
    155 KB · Views: 6
  • Slide2.JPG
    Slide2.JPG
    160.6 KB · Views: 6
  • Slide3.JPG
    Slide3.JPG
    163.8 KB · Views: 4
  • Slide4.JPG
    Slide4.JPG
    161.3 KB · Views: 5

rbrasill

New Member
Joined
Apr 15, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,575
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top