Filter Data



to filter the data
Sub Analyst()
Dim role As String
role = Sheets(1).Cells(3, 2).Value

Sheets(2).AutoFilterMode = False

 Sheets(2).UsedRange.AutoFilter Field:=11, Criteria1:="=*" & role & "*" 'Operator:=xlAnd, Field:=9, Criteria2:="<>*" & role & "*"
'Sheets(2).UsedRange.AutoFilter Field:=11, Criteria1:="*"role, Operator:=xlAnd, Field:=9, Criteria2:="<>*role*"
Sheets(2).UsedRange.AutoFilter Field:=9, Criteria1:="<>*" & role & "*"
Sheets(2).UsedRange.Copy Sheets(1).Range("a11")
Sheets(1).AutoFilterMode = False


End Sub

Comments

Post a Comment

Popular posts from this blog

Check folder exist or not in excel vba

Check if Folder Exists using Excel VBA 'In this Example I am checking if "C:\Temp" exits Sub sbCheckingIfAFolderExists() Dim FSO Dim sFolder As String sFolder = "C:\Temp" ' You can Specify Any Folder To Check It Set FSO = CreateObject("Scripting.FileSystemObject") If FSO.FolderExists(sFolder) Then MsgBox "Specified Folder Is Available", vbInformation, "Exists!" Else MsgBox folder &"Specified Folder Not Found", vbInformation, "Not Found!" End If End Sub ------------------------ Opening Folders using VBA Excel 'In this Example I am Opening a Folder ("C:\Temp") Sub sbOpeningAFolder() Dim FSO Dim sFolder As String sFolder = "C:\Temp" 'You can specify your Folder which you wants to Open Set FSO = CreateObject("Scripting.FileSystemObject") If Not FSO.FolderExists(sFolder) Then MsgBox "Specified Folder Not Found", vbInformation, ...
Read more

Excel to notepad

Sub TestNotePad()     '// The range to copy - written freehand so change as needed     Range("A1:B55").Copy         '// Start Notepad with focus     Shell "notepad.exe", vbNormalFocus         '// Send the standard CTRL+V. Pastes to the     '// active window (Notepad, hopefully)     SendKeys "^V"     '// Back to the top of the file     SendKeys "^{HOME}"     End Sub ------------------- cebu1014 Author Commented: 2013-05-22 I got it to work using your above routine as a guide. I removed some of the lines though and added  TRUE at end of sendkeys command in order to get it to work. Sub CopyToNotepad()              Range("A1").Select     Range(Selection, Selection.End(xlDown)).Select     Selection.Copy       Shell "Notepad C:\Users\mw1\my Documents\abc.csv", vbNor...
Read more

combine sheets into one book and all sheets into one sheet

combine sheets into one book and all sheets into one sheet Sub getdata_Fromallbooks() ' Path = ThisWorkbook.Path & "\st\" Filename = Dir(Path & "*.xls")   Do While Filename <> ""   Workbooks.Open Filename:=Path & Filename, ReadOnly:=True      For Each Sheet In ActiveWorkbook.Sheets      Sheet.Copy After:=ThisWorkbook.Sheets(1)   Next Sheet      Workbooks(Filename).Close      Filename = Dir()   Loop End Sub Sub Combine()     Dim J As Integer     On Error Resume Next     Sheets(1).Select     Worksheets.Add ' add a sheet in first place     Sheets(1).Name = "Combined"     ' copy headings     Sheets(2).Activate     Range("A1").EntireRow.Select     Selection.Copy Destination:=Sheets(1).Range("A1")     ' work through sheets     For J = 2 To Sheets.Count ' ...
Read more