Here this is my code
Option Explicit
Private Sub CommandButton1_Click()
Dim i As Long, msg As String, x, rng As Range, WS As Worksheet
For i = 6 To 6
If Me.Controls("Combobox" & i).ListIndex = -1 Then
msg = "All the comboboxes has to be selected"
Exit For
End If
Next
If Len(msg) Then
MsgBox msg: Exit Sub
End If
Application.ScreenUpdating = False
Set WS = Sheets.Add
x = VBA.Array("c", "f", "j", "l", "as", "ay")
With Sheets("ONSITECASES").Cells(1).CurrentRegion
Set rng = .Offset(, .Columns.Count + 1).Cells(1)
For i = 0 To UBound(x)
.Cells(1, x(i)).Copy rng.Offset(, i)
rng.Offset(1, i).Value = Me.Controls("Combobox" & i + 1).Value
Next
.AdvancedFilter 2, rng.CurrentRegion, WS.Cells(1)
rng.CurrentRegion.EntireColumn.Clear
End With
WS.Copy
With ActiveWorkbook
.ActiveSheet.Name = "ONSITE"
.SaveAs ThisWorkbook.Path & "\SLC" & Me.ComboBox4.Value & "_" & Me.ComboBox6.Value & ".xls"
.Close False
End With
With Application
.DisplayAlerts = False
WS.Delete
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim x, i As Long
x = VBA.Array("c", "f", "j", "l", "as", "ay")
With Sheets("ONSITECASES").Cells(1).CurrentRegion.Offset(1)
For i = 0 To UBound(x)
Me.Controls("ComboBox" & i + 1).List = Filter(.Parent.Evaluate("transpose(if(countif(offset(" & _
.Columns(x(i)).Address & ",0,0,row(1:" & .Rows.Count & "))," & _
.Columns(x(i)).Address & ")=1," & .Columns(x(i)).Address & _
",char(2)))"), Chr(2), 0)
Next
End With
End Sub
i want assig my new workbook will save in particular folder..
this my path(D:\Documents and Settings\Administrator\Desktop\FORUMINPUT\REBV\FOLDER) where i want save my new workbook.
Bookmarks