I have 2 Worksheets (Sheet1 and Sheet2) with 10 columns and thousands of rows.
Based on a data item in column 7 I need to filter this data by this data item and then copy the entire rows matching this data item and paste onto a new worksheet (Which is named by this value). This will be repeated for 15 different data items. (Column 7 has maybe 100 different data items in it)
I thought of three different ways I could do this.....
1. I recorded a macro as below using Auto filter. I would need to have the criteria as a variable and also the rows that the auto filter has selected. But Im not familiar with the AutoFilter Object and I dont understand the help given by Microsoft.
Sheets("Sheet1").Select Rows("1:1").Select Selection.AutoFilter Selection.AutoFilter Field:=7, Criteria1:="E WARWIC/7230" Rows("3355:3373").Select Selection.Copy Sheets("7230").Select Rows("2:2").Select ActiveSheet.Paste Sheets("Sheet2").Select Rows("1:1").Select Application.CutCopyMode = False Selection.AutoFilter Selection.AutoFilter Field:=7, Criteria1:="E WARWIC/7230" Rows("1243:1524").Select Range("A1524").Activate Selection.Copy Sheets("7230").Select Rows("5:5").Select ActiveSheet.Paste
2. I have some code which deletes a row based on the criteria if the cell is equal to 4. Maybe I could adjust this to the 15 data items as the criteria. I dont mind deleting the data which doesnt match either of the 15 data items, in fact this would be better because this would reduce the size of my workbook...its pretty big already!
Sub enterdata() Dim myRows As Integer Dim i As Integer Application.ScreenUpdating = False Range("A3").Select Selection.End(xlDown).Select myRows = ActiveCell.Row Range("c3").Select i = 3 While i <= myRows If ActiveCell.Value = 4 Then Selection.EntireRow.Delete End If If ActiveCell.Value = 8 Then ActiveCell.Offset(1, 0).Select End If i = i + 1 Wend Application.ScreenUpdating = True End Sub
3. I am being really dumb and there is a relly easy way that I have forgotten about!
I think there is a brilliant example on this forum but its an old one and I cant read it due to all the &s and the smily faces that have ended up in the code!!
Anyway ...Im sure Ive rambled on enough and there will need to be more clarification....
Thanks to anyone that can help!!!