hi
Is there any way i need to filter particular / specific word in particular column like("A column") and select all(Filtered data), cut and paste , create another sheet in same workbook and paste into it.
for example
Need to filter "MPS" in code column and move those filtered in another sheet by creating new sheet
ode | address | Part |
PIT-000000013000835326 | ||
TAP-000000012502567470 | ||
2FRE-65-00-MPS-020 | ||
ALF-00000001250258666 | ||
ALF-000000012502612937 | ||
2FRE-65-00-MPS-031|2FRE-65 | ||
ALF-000000012502621977 | ||
TAP-00000001500557215 |
for this i am using this below code
Code
Application.ScreenUpdating = FalseDim x As Range Dim rng, rng1 As Range Dim last As Long Dim sht As String 'specify sheet name in which the data is stored sht = "Sheet1" 'change filter column in the following code last = Sheets(sht).Cells(Rows.Count, "A").End(xlUp).Row Set rng = Sheets(sht).Range("A1:F" & last) Set rng1 = Worksheets("Need to be removed MPS").Range("B2") For Each x In rng1 'Range(Sheets("Need to be removed 0").B2) '([L2], Cells(Rows.Count, "L").End(xlUp)) ' K column has some specific value in excel so macro will create only those value's extra sheet With rng .AutoFilter .AutoFilter Field:=1, Criteria1:=x.Value .SpecialCells(xlCellTypeVisible).Copy Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value ActiveSheet.Paste End With Next x ' Turn off filter Sheets(sht).AutoFilterMode = False With Application .CutCopyMode = False .ScreenUpdating = True End With
heaps thanks in advance
thanks