VBA_Separate_value_Material

[9kongpob]

Sub Separate_Mat(cb_value)

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

'-------------------------------------------------- 'separate value mat 'adjust column / change fill color

Dim i As Integer

For i = 1 To 5
    Range("I:I").Insert
    Range("I:I").Interior.ColorIndex = 36
Next i

End Sub

[9kongpob]

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

[9kongpob]

[9kongpob]