Closed 9kongpob closed 3 years ago
Sub Separate_Mat(cb_value)
Dim sh As Worksheet
Dim last_row As Long
Sheets("2021").Select
Worksheets("2021").Range("A6").AutoFilter Field:=1, Criteria1:=cb_value
Range("A5").Select
Selection.End(xlDown).Select
Range("Y" & ActiveCell.Row & ":A" & 5).Select
Selection.Copy
Sheets(cb_value).Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
last_row = Cells.Find(What:="*", SearchDirection:=xlPrevious).Row
'-------------------------------------------------- 'separate value mat 'adjust column / change fill color
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim u As Integer
'=============================== For i = 1 To 5 Range("I:I").Insert Range("I2:I" & last_row).Interior.ColorIndex = 36 Range("I2:I" & last_row).NumberFormat = "@" Next i
For j = 1 To 5
Cells(2, 8 + j).Value = "M_0" & j
Next j
'===============================
'MsgBox ("Check last_row : " & last_row)
For k = 3 To last_row
Cells(k, 9).Value = Left(Cells(k, 8).Value, InStr(1, Cells(k, 8).Value, "x", vbTextCompare) - 1)
Cells(k, 10).Value = Mid(Cells(k, 8).Value, (InStr(1, Cells(k, 8).Value, "x", vbTextCompare)), 1)
Cells(k, 11).Value = Mid(Cells(k, 8).Value, InStr(1, Cells(k, 8).Value, "x", vbTextCompare) + 1, (InStrRev(Cells(k, 8).Value, "x", , vbTextCompare) - InStr(1, Cells(k, 8).Value, "x", vbTextCompare) - 1))
Cells(k, 12).Value = Mid(Cells(k, 8).Value, (InStrRev(Cells(k, 8).Value, "x", , vbTextCompare)), 1)
Cells(k, 13).Value = Mid(Cells(k, 8).Value, InStrRev(Cells(k, 8).Value, "x", , vbTextCompare) + 1)
Next k
'Range("A2").Select
'Range("I" & 2 & ":M" & last_row).Interior.ColorIndex = 36
Range("A2").Select
End Sub
Private Sub UserForm_Initialize()
ComboBox_Vendor.AddItem "AMT"
ComboBox_Vendor.AddItem "FTPT"
ComboBox_Vendor.AddItem "GTP"
ComboBox_Vendor.AddItem "QPS"
ComboBox_Vendor.AddItem "SASC"
ComboBox_Vendor.AddItem "SMTL"
ComboBox_Vendor.AddItem "SPI"
ComboBox_Vendor.AddItem "SSSC"
End Sub
Private Sub Cancel_Click()
Unload Vendor_Select
End Sub
Private Sub separate_value_Click()
Dim sheet As Worksheet
Unload Vendor_Select
For Each sh In Sheets
If sh.Name = ComboBox_Vendor.Value Then
Application.DisplayAlerts = False
Sheets(ComboBox_Vendor.Value).Delete
Application.DisplayAlerts = True
End If
Next sh
'Sheets.Add After:=Sheets(Sheets.count)
'ActiveSheet.Name = ComboBox_Vendor.Value
Sheets.Add(After:=Sheets(Sheets.count)).Name = ComboBox_Vendor.Value
Call Separate_Mat.Separate_Mat(ComboBox_Vendor.Value)
End Sub
'-------------------------------------- 'version 1.0.0 'Date : 20210309 11:20 'Selection Vendor to create ws name vendor 'Separate value material size '--------------------------------------
Sub Mat_Separate()
Vendor_Select.Show
End Sub
Sub Separate_Mat(cb_value)
'-------------------------------------------------- 'separate value mat 'adjust column / change fill color
'=============================== For i = 1 To 5 Range("I:I").Insert Range("I2:I" & last_row).Interior.ColorIndex = 36 Range("I2:I" & last_row).NumberFormat = "@" Next i
'===============================
End Sub