Snasui Excel Forum

Code: Select all

Public Sub SaveStorage()
Dim i, r As Integer
Dim Tag, Day, MatCode, Pallet, Des, Quan, Loc, Note, Batch, Who As String

r = 5
    Tag = Sheets(1).Cells(5, 4).Value
    Day = Sheets(1).Cells(5, 7).Value
    MatCode = Sheets(1).Cells(7, 4).Value
    Pallet = Sheets(1).Cells(7, 7).Value
    Des = Sheets(1).Cells(9, 4).Value
    Quan = Sheets(1).Cells(9, 7).Value
    Loc = Sheets(1).Cells(11, 4).Value
    Note = Sheets(1).Cells(11, 7).Value
    Batch = Sheets(1).Cells(13, 4).Value
    Who = Sheets(1).Cells(13, 7).Value
        r = r + 1
            Sheets(3).Cells(r, 1) = Day
            Sheets(3).Cells(r, 2) = Loc
            Sheets(3).Cells(r, 3) = Tag
            Sheets(3).Cells(r, 4) = MatCode
            Sheets(3).Cells(r, 5) = Des
            Sheets(3).Cells(r, 6) = Batch
            Sheets(3).Cells(r, 7) = Pallet
            Sheets(3).Cells(r, 8) = Quan
            Sheets(3).Cells(r, 9) = Who
            Sheets(3).Cells(r, 10) = Note
    End Sub

Code: Select all

Sub myStatus()
    Dim lastrow As String, wb As Workbook
    Dim mylookup As Range, tb As Workbook
    Dim Location(1728, 2), Batch(1728, 4), Qty(1728, 5) As String
    Dim i As Integer
    Dim TagNo(1728, 3) As String
        Set tb = ThisWorkbook
            With tb.Sheets(1)
                lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
            End With
        Set wb = Workbooks.Open("C:\WorkAA\Database\Database.xlsm")
        Set myrange = wb.Sheets(1).Range("A2:L1278")
    On Error Resume Next
        With tb.Sheets(1)
            .Cells(7, 4).Value = Application.WorksheetFunction.VLookup(.[D5], myrange, 7, 0)
            .Cells(9, 4).Value = Application.WorksheetFunction.VLookup(.[D5], myrange, 9, 0)
            .Cells(11, 4).Value = Application.WorksheetFunction.VLookup(.[D5], myrange, 4, 0)
            .Cells(13, 4).Value = Application.WorksheetFunction.VLookup(.[D5], myrange, 10, 0)
            .Cells(7, 7).Value = Application.WorksheetFunction.VLookup(.[D5], myrange, 6, 0)
            .Cells(9, 7).Value = Application.WorksheetFunction.VLookup(.[D5], myrange, 12, 0)
            .Cells(13, 7).Value = Application.WorksheetFunction.VLookup(.[D5], myrange, 5, 0)
            .Cells(5, 7).Value = Application.WorksheetFunction.VLookup(.[D5], myrange, 11, 0)
                With tb.Sheets(4)
                    For i = 1 To 1728
                        Location(i, 2) = Sheets(4).Cells(i + 1, 2).Value
                        TagNo(i, 3) = Sheets(4).Cells(i + 1, 3).Value
                        Batch(i, 4) = Sheets(4).Cells(i + 1, 4).Value
                        Qty(i, 5) = Sheets(4).Cells(i + 1, 5).Value
                    Next i
                         For i = 1 To 1728
                             If Location(i, 2) = Sheets(1).Cells(11, 4).Value Then
                                 TagNo(i, 3) = Sheets(1).Cells(5, 4).Value
                                 Batch(i, 4) = Sheets(1).Cells(13, 4).Value
                                Qty(i, 5) = Sheets(1).Cells(9, 7).Value
                            End If
                         Next i
                End With
        End With
    wb.Close False
End Sub

Code: Select all

Public Sub SaveStorage

Code: Select all

Public Sub SaveStorage()
Dim i, r As Integer
Dim Tag, Day, MatCode, Pallet, Des, Quan, Loc, Note, Batch, Who As String

Code: Select all

 r = Sheets(3).Range("a" & Rows.Count).End(xlUp).Row

Code: Select all

Public Sub SaveStorage()
Dim i, r As Integer
Dim Tag, Day, MatCode, Pallet, Des, Quan, Loc, Note, Batch, Who As String
r = Sheets(3).Range("a" & Rows.Count).End(xlUp).Row
    Tag = Sheets(1).Cells(5, 4).Value
    Day = Sheets(1).Cells(5, 7).Value
    MatCode = Sheets(1).Cells(7, 4).Value
    Pallet = Sheets(1).Cells(7, 7).Value
    Des = Sheets(1).Cells(9, 4).Value
    Quan = Sheets(1).Cells(9, 7).Value
    Loc = Sheets(1).Cells(11, 4).Value
    Note = Sheets(1).Cells(11, 7).Value
    Batch = Sheets(1).Cells(13, 4).Value
    Who = Sheets(1).Cells(13, 7).Value
        r = r + 1
            Sheets(3).Cells(r, 1) = Day
            Sheets(3).Cells(r, 2) = Loc
            Sheets(3).Cells(r, 3) = Tag
            Sheets(3).Cells(r, 4) = MatCode
            Sheets(3).Cells(r, 5) = Des
            Sheets(3).Cells(r, 6) = Batch
            Sheets(3).Cells(r, 7) = Pallet
            Sheets(3).Cells(r, 8) = Quan
            Sheets(3).Cells(r, 9) = Who
            Sheets(3).Cells(r, 10) = Note
    End Sub

Code: Select all

Public Sub SaveStorage()
Dim i, r As Integer
Dim Tag, Day, MatCode, Pallet, Des, Quan, Loc, Note, Batch, Who As String
 r = Sheets(3).Range("a" & Rows.Count).End(xlUp).Row
    Tag = Sheets(1).Cells(5, 4).Value
    Day = Sheets(1).Cells(5, 7).Value
    MatCode = Sheets(1).Cells(7, 4).Value
    Pallet = Sheets(1).Cells(7, 7).Value
    Des = Sheets(1).Cells(9, 4).Value
    Quan = Sheets(1).Cells(9, 7).Value
    Loc = Sheets(1).Cells(11, 4).Value
    Note = Sheets(1).Cells(11, 7).Value
    Batch = Sheets(1).Cells(13, 4).Value
    Who = Sheets(1).Cells(13, 7).Value
        r = r + 1
            Sheets(3).Cells(r, 1) = Day
            Sheets(3).Cells(r, 2) = Loc
            Sheets(3).Cells(r, 3) = Tag
            Sheets(3).Cells(r, 4) = MatCode
            Sheets(3).Cells(r, 5) = Des
            Sheets(3).Cells(r, 6) = Batch
            Sheets(3).Cells(r, 7) = Pallet
            Sheets(3).Cells(r, 8) = Quan
            Sheets(3).Cells(r, 9) = Who
            Sheets(3).Cells(r, 10) = Note
             i = Application.WorksheetFunction.Match(Sheets(1).Cells(11, 4), Sheets(4).Range("B2:B1728"), 0) + 1
            Sheets(4).Cells(i, 3).Value = Sheets(1).Cells(5, 4).Value
            Sheets(4).Cells(i, 4).Value = Sheets(1).Cells(13, 4).Value
            Sheets(4).Cells(i, 5).Value = Sheets(1).Cells(9, 7).Value
    End Sub