Snasui Excel Forum

Code: Select all

Sub AutoShape1_Click()    'save input
Dim r As Double, rr As Integer, mc As String
Dim mo As String, pa As String, shift As String, sec As String
If Sheet1.Range("B5") <> "" Then
rr = 5
mo = ""
pa = ""
sec = ""
Do While Sheet1.Cells(rr, 1) <> ""
If Sheet1.Cells(rr, 2) <> "" And sec <> Sheet1.Cells(rr, 2) Then
    SH = check_pro(Sheet1.Cells(rr, 2))
    sec = Sheet1.Cells(rr, 2)
    r = check_row(3)
End If
'=======================================================
    Sheets(SH).Cells(r, 1) = Sheet1.Range("C1")
    Sheets(SH).Cells(r, 2) = Sheet1.Range("D1")
    Sheets(SH).Cells(r, 3) = Sheet1.Range("E1")
    '=================================
    If Sheet1.Cells(rr, 3) <> "" Then
        mo = Sheet1.Cells(rr, 4)
        pa = Sheet1.Cells(rr, 3)
        'mc = Sheet1.Cells(rr, 9)
        'shift = Sheet1.Cells(rr, 7)
    End If
    Sheets(SH).Cells(r, 4) = Sheet1.Cells(rr, 7)  'shift
    Sheets(SH).Cells(r, 5) = mo  ' model
    Sheets(SH).Cells(r, 6) = pa  ' part name
    Sheets(SH).Cells(r, 7) = Sheet1.Cells(rr, 12)  'input
    Sheets(SH).Cells(r, 17) = Sheet1.Cells(rr, 17)  'output
    Sheets(SH).Cells(r, 9) = Sheet1.Cells(rr, 14)   'ng
    Sheets(SH).Cells(r, 10) = Sheet1.Cells(rr, 15)  'ng code
    Sheets(SH).Cells(r, 11) = Sheet1.Cells(rr, 16)  'ng name
    Sheets(SH).Cells(r, 12) = Sheet1.Cells(rr, 17)  'ng amount
    Sheets(SH).Cells(r, 14) = Sheet1.Cells(rr, 8)   'part no.
    Sheets(SH).Cells(r, 15) = Sheet1.Cells(rr, 11)  'inventory
    'Sheets(SH).Cells(r, 16) = Sheet1.Cells(rr, 9)   'MC
    Sheets(SH).Cells(r, 9) = Sheet1.Cells(rr, 9)
    If SH = "DLC" Then
        Sheets(SH).Cells(r, 18) = Sheet1.Cells(rr, 10)   'Opt
    Else
        Sheets(SH).Cells(r, 17) = Sheet1.Cells(rr, 10)   'Opt
    End If
    If SH = "DLC" Then Sheets(SH).Cells(r, 19) = Sheet1.Range("L1")  ' Type
    Sheets(SH).Cells(r, 13) = sec
    r = r + 1
    rr = rr + 1
Loop
End If
'=======================================================
    Sheet1.Range("B5:K1000") = ""
    Sheet1.Range("M5:O1000") = ""
    Sheet1.Range("Q5:Q1000") = ""

End Sub