Option Explicit
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim filterRng As Range
Dim rngVisible As Range
Dim cl As Range
Dim X As Long
Dim R As Long
Private Sub UserForm_Initialize()
With ListBox1
.AddItem "English"
.AddItem "German"
.AddItem "Portuguese"
.AddItem "Russian"
.AddItem "Chinese"
End With
With ListBox2
.AddItem "English"
.AddItem "German"
.AddItem "Portuguese"
.AddItem "Russian"
.AddItem "Chinese"
.AddItem "(No 2nd Language)"
End With
ListBox1.ColumnWidths = "75;0"
ListBox2.ColumnWidths = "100;0"
Set ws1 = ThisWorkbook.Sheets("SL1-9")
Set ws2 = ThisWorkbook.Sheets("SCS_List for SAP P&E")
End Sub
Private Sub OKButton_Click()
Sheets("SCS_List for SAP P&E").Range("B2:M3000").ClearContents
Set filterRng = ws1.Cells(1, 1).CurrentRegion
filterRng.AutoFilter
filterRng.AutoFilter Field:=14, Criteria1:="YES"
On Error Resume Next
With ws1
Set rngVisible = .Range(.Cells(3, 3), .Cells(.Rows.Count, 3).End(xlUp))
X = 2
For Each cl In rngVisible
If cl.EntireRow.Hidden = False Then
R = cl.Row
.Cells(R, 1).Value = UserSelection.TextBox1.Value
ws2.Cells(X, 2).Value = .Cells(R, 1).Value
ws2.Cells(X, 3).Value = .Cells(R, 2).Value & .Cells(R, 3).Value
ws2.Cells(X, 4).Value = .Cells(R, 4).Value & .Cells(R, 5).Value
ws2.Cells(X, 5).Value = .Cells(R, 7).Value & .Cells(R, 8).Value & .Cells(R, 9).Value
ws2.Cells(X, 6).Value = .Cells(R, 10).Value & .Cells(R, 11).Value & .Cells(R, 12).Value
Select Case UserSelection.ListBox1.Value
Case "English"
ws2.Cells(X, 9).Value = .Cells(R, 18).Value ' English
Case "German"
ws2.Cells(X, 9).Value = .Cells(R, 17).Value ' German
Case "Portuguese"
ws2.Cells(X, 9).Value = .Cells(R, 20).Value ' Portuguese
Case "Russian"
ws2.Cells(X, 9).Value = .Cells(R, 21).Value ' Russian
Case "Chinese"
ws2.Cells(X, 9).Value = .Cells(R, 22).Value ' Chinese
End Select
Select Case UserSelection.ListBox2.Value
Case "English"
ws2.Cells(X, 10).Value = .Cells(R, 18).Value ' English
Case "German"
ws2.Cells(X, 10).Value = .Cells(R, 17).Value ' German
Case "Portuguese"
ws2.Cells(X, 10).Value = .Cells(R, 20).Value ' Portuguese
Case "Russian"
ws2.Cells(X, 10).Value = .Cells(R, 21).Value ' Russian
Case "Chinese"
ws2.Cells(X, 10).Value = .Cells(R, 22).Value ' Chinese
Case "(No 2nd Language)"
ws2.Cells(X, 10).Value = .Cells(R, 55).Value ' (No 2nd Language)
End Select
X = X + 1
End If
Next cl
End With
On Error GoTo 0
Unload Me
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub
Bookmarks