Hey Team,
I'm trying to run a macro that creates a Missing Materials Report based on a specific Job Number. What I want it to do is create a new workbook then copy all rows associated with a specific job number from the master workbook sheet "Open" to the new workbook. Then once the data is in the new workbook I want it to look at the material ordered and the material received. All rows where the material received is equal to or higher than the material ordered can be deleted. All rows with the Material received lower than the material ordered need to stay, then subtract the received from the ordered to get the quantity needed.
I was able to get a good start by creating a new workbook and saving with the correct file name. I'm just not sure how to take it forward from here.
The way it works currently is I select the Job Number from the %Received Tab in the Master then the workbook is created and saved into active workbook file path & \Reports Folder.
- See Link to Workbook.
At the very least if I could simply get it to copy all rows that have the selected job number over to the new workbook I can work with from there.
Thanks for the assistance,
I'm trying to run a macro that creates a Missing Materials Report based on a specific Job Number. What I want it to do is create a new workbook then copy all rows associated with a specific job number from the master workbook sheet "Open" to the new workbook. Then once the data is in the new workbook I want it to look at the material ordered and the material received. All rows where the material received is equal to or higher than the material ordered can be deleted. All rows with the Material received lower than the material ordered need to stay, then subtract the received from the ordered to get the quantity needed.
I was able to get a good start by creating a new workbook and saving with the correct file name. I'm just not sure how to take it forward from here.
The way it works currently is I select the Job Number from the %Received Tab in the Master then the workbook is created and saved into active workbook file path & \Reports Folder.
- See Link to Workbook.
At the very least if I could simply get it to copy all rows that have the selected job number over to the new workbook I can work with from there.
VBA Code:
Sub BLC_JobNumber_Report()
Dim foundCell As Range
Dim firstAddress As String
Dim searchRng As Range
Dim thisWb As Workbook
Dim selJobNr As String
Dim selClient As String
Dim selProj As String
Dim NameBase As String
selJobNr = ActiveSheet.Cells(ActiveCell.Row, "B").Value
selClient = ActiveSheet.Cells(ActiveCell.Row, "C").Value
selProj = ActiveSheet.Cells(ActiveCell.Row, "D").Value
NameBase = Format(Date, "mm.dd.yyyy")
userAnswer = MsgBox("Run missing material report for job number " & selJobNr & "?", vbQuestion + vbYesNo, "User Repsonse")
If userAnswer = vbYes Then
Set thisWb = ActiveWorkbook
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=thisWb.Path & "\Reports\" & selJobNr & " " & selClient & " " & selProj & "- " & NameBase & ".xlsx"
End If
End Sub
Thanks for the assistance,