Thread: production report update from packing report.

dear friends i want to update my first sheet from second sheet.second sheet our packing report & it's daily I'm getting from packing department.according to packing report i want to update production sheet.second sheet Column "F" first 6 characters i need to mach with first sheet column "K" first 6 characters.if it is match then first sheet column "J" values need to reduce form second sheet column "G".
ex- first sheet value = 60
packing report value = 50 then answer in first sheet column "J" =60-50 = 10
after updating first sheet column "J" any value coming mines then that row need to delete.
pls make macro for this.thanks..

This should do it:

Code:

Option Explicit

Sub PackingToProductionUpdate()
Dim LotFind As Range, Lots As Range, LotNum As Range
Application.ScreenUpdating = False

Set Lots = Sheets("Packing").Range("F5", Range("F" & Rows.Count).End(xlUp))
On Error Resume Next

For Each LotNum In Lots
    With Sheets("Production")
        Set LotFind = .Columns("K:K").Find(Left(LotNum, 6), After:=.Range("K" & Rows.Count), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        
        If Not LotFind Is Nothing Then
            LotFind.Offset(0, -1).Value = LotFind.Offset(0, -1).Value - LotNum.Offset(0, 1).Value
            LotNum.EntireRow.ClearContents
            Set LotFind = Nothing
        End If
    
    End With
Next LotNum

Application.ScreenUpdating = True
End Sub

This might be what you need.

Code:

Sub Process()
For N = 5 To Sheets("PACKING").Cells(Rows.Count, 1).End(xlUp).Row
    If Application.CountIf(Sheets("PRODUCTION").Columns(11), Left(Sheets("PACKING").Cells(N, 6), 6)) = 1 Then
        Set TargetCell = Sheets("PRODUCTION").Columns(11).Find(Left(Sheets("PACKING").Cells(N, 6), 6), , xlValues, xlPart)
        TargetCell.Offset(0, -1) = TargetCell.Offset(0, -1) - Sheets("PACKING").Cells(N, 7)
        If TargetCell.Offset(0, -1) < 0 Then TargetCell.EntireRow.Delete
    End If
Next N
End Sub

dear JB thanks u very much for your helps.i have two problems.one is below code giving some error.other one is both sheet values are equals then first sheet that row also need to delete.i mean after minus first reports values from second sheet values answers are zero or minus then that all rows should delete,

Code:

Set Lots = Sheets("Packing").Range("F5", Range("F" & Rows.Count).End(xlUp))

Here's the update to delete the PRODUCTION row if the value drops to zero or below:

Code:

Option Explicit

Sub PackingToProductionUpdate()
Dim LotFind As Range, Lots As Range, LotNum As Range
Dim LotVal As Double
Application.ScreenUpdating = False

Set Lots = Sheets("Packing").Range("F5", Range("F" & Rows.Count).End(xlUp))
On Error Resume Next

For Each LotNum In Lots
    With Sheets("Production")
        Set LotFind = .Columns("K:K").Find(Left(LotNum, 6), After:=.Range("K" & Rows.Count), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        If Not LotFind Is Nothing Then
            LotVal = LotFind.Offset(0, -1).Value - LotNum.Offset(0, 1).Value
                If LotVal <= 0 Then
                    LotFind.EntireRow.Delete xlShiftUp
                Else
                    LotFind.Offset(0, -1).Value = LotVal
                End If
            LotNum.EntireRow.ClearContents
            Set LotFind = Nothing
        End If
    End With
Next LotNum

Application.ScreenUpdating = True
End Sub

dear JB everything work very well.just one adjustment i need.some values u reducing form first sheet column "K" that values can i see,i mean not final answer.if i click that cell it should below example.then i can understand how much packed & how much balance to pack.
ex- =4500-500

Um.........huh?

If I can do what?

My reply to your post #7 is summed up in post #8.

Ah...the lights have got on. Now I see what you mean. Nice work, John!