Issue making macro or formula on my spreedsheet

[devan69]

hello and thanks to anyone willing to help me. with my work i deal with alot of parts to make a valve. It is a family business and still old fashion and not to into computers as they should lol. i'm working on a spreedsheet to better track the parts i am missing from a order so the shop guys know what need to be made.

first sheet is search and add. that is the start. this sheet is good all buttons done like i want. after adding to the list i click save and it makes a copy on sheet 4 labeled orders.
3rd sheet labeled parts needed

what i am trying to do is have it when i save the we will call 'job card' to the orders sheet(save each job card under each other like picture shows) it will then either automatically or via a update button take the part number, quantity, and the due date (currently i have column E hidden that has the due date for that job card at end of each row for each part. not sure if that helps) and put it into sheet labeled parts needed and put them in there respective sections ie: hoods in hoods sleeves in sleeves and so on. also since each job may have the same items (part numbers ie: 1-110-04 not type ie: hood) if it can combine them from each card to show a total needed accross all job cards and display the earliest due date which would be displayed in column E in orders sheet. That way when we complete a order and i delete the job card and if need be press update again the total need and due date will update. if need more info let me know. i suck at explaining things lol. if someone is willing to help me and would rather i do live stream or something i am open to it. thanks again.

 

[devan69]

ok so got a somewhat of what i was trying to get done but not working 100% and also issue with other button on search and add sheet. here is the code i have so far for the parts list.

Sub Partslist()
Dim Sheet1 As String
Dim Sheet2 As String
Dim Range1 As String
Dim Range2 As String
Dim CriteriaColumn As Integer
Sheet1 = "orders"
Sheet2 = "parts needed"
Range1 = "B25:E1000"
Range2 = "A3"
CriteriaColumn = 6
Dim Rng1 As Range
Dim Rng2 As Range
Set Rng1 = Sheets(Sheet1).Range(Range1)
Set Rng2 = Sheets(Sheet2).Range(Range2)
Count = 0
For i = 1 To Rng1.Rows.Count
If Rng1.Cells(i, CriteriaColumn) = hood Then
Count = Count + 1
Rng1.Rows(i).Copy
Rng2.Cells(Count, 1).PasteSpecial Paste:=xlPasteAll
End If
Next i
Application.CutCopyMode = False
End Sub

now this is copying the type, part number, quantity and the date. 2 issues. 1) it it just copying each row. not just the rows with the "hood" in the type column. 2) the date isn't referencing correctly. on sheet 1 i just have column e as formula =B7. cause that is were i would enter the due date for the job card. when i save the job card the fomula follow to the new card on the orders sheet. which works for the orders sheet but breaks when copying the parts to the parts needed sheet with due date cause the formula is = function and the originating cell isn't on that sheet. here is the button i use to copy from search and add to parts need.

Sub OrderList()

Application.ScreenUpdating = False

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("search and add")
Set pasteSheet = Worksheets("orders")

copySheet.Range("B3:F33").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(22, 0).PasteSpecial

Application.CutCopyMode = False

Worksheets("search and add").Range("C4:E33").ClearContents
Worksheets("search and add").Range("B5").ClearContents
Worksheets("search and add").Range("B7").ClearContents
Worksheets("search and add").Range("B9").ClearContents
Worksheets("search and add").Range("B11").ClearContents
Worksheets("search and add").Range("B13:B33").ClearContents
Worksheets("search and add").Range("C4").Select

Application.ScreenUpdating = True
End Sub

I know probably better way to write it but i'm still learning lol. is there a way to have ti just copy the color, border, and text. that way the dates are in the E column still but not as a formula just text and that would fix the date issue for issue 2. I would then just need to fix the just copying the rows that had the key word ei: hood, bonnet, sleeve, and so on and get it to show how many total of each part needed from all the orders and then keey the earliest due date.

 

[devan69]

sorry doesn't let me edit my post. i did figure out how to get it to copy from the search and add sheet everything but the formulas. so now just gotta get it to only copy the rows with the same key word into there columns and the quantities to total across all the orders and lastly to have the due date to display the earliest due date for each item.