I have a sheet with 10 columns. In column A the values can be anything but are often repeated. I want to select all the rows having the same value in column A to be copied and pasted in a new sheet(preferably with the sheet name as the column A value). I know how to set the criteria if the cell values in A are pre defined.. In my case they keep changing. for example if criteria to sort is known as 0517 i can do this..But i dont know all the values in column A to use this code multiple times for each value
Cells.AutoFilter
Cells.AutoFilter Field:=2, Criteria1:="0517"
Range("A2:J" & LR).SpecialCells(xlCellTypeVisible).Select
Range("A2:J" & LR).Copy
Sheets(0517).Activate
Cells(ALR, 1).Activate
ActiveSheet.Paste
Sheets(sheet1).Activate
Cells.AutoFilter
End Sub
Cells.AutoFilter
Cells.AutoFilter Field:=2, Criteria1:="0517"
Range("A2:J" & LR).SpecialCells(xlCellTypeVisible).Select
Range("A2:J" & LR).Copy
Sheets(0517).Activate
Cells(ALR, 1).Activate
ActiveSheet.Paste
Sheets(sheet1).Activate
Cells.AutoFilter
End Sub