Hi
I have this macro that filters out data by using criterial
The macro at the moment goes into sheets 1 & 2 and filters out. What im trying to do is to compare the data between the 2 sheets and if data dont match I need whats missing from sheet 2 highlighted on sheet 1.
I also need to set it up so it goes all the way through the workbook selecting each unique criterial number but before it moves onto next number to filter I need it to pause and i have to push a key for it to continue and move onto next. Any ideas?
I have this macro that filters out data by using criterial
The macro at the moment goes into sheets 1 & 2 and filters out. What im trying to do is to compare the data between the 2 sheets and if data dont match I need whats missing from sheet 2 highlighted on sheet 1.
I also need to set it up so it goes all the way through the workbook selecting each unique criterial number but before it moves onto next number to filter I need it to pause and i have to push a key for it to continue and move onto next. Any ideas?
Code:
Sub Macro1()
'
' Macro1 Macro
'
'
Cells.Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add2 Key:=Range( _
"D1:D1039310"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range("A1:D1039310")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Sheet1").Select
Cells.Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"D1:D1039310"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:D1039310")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Sheet2").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$1039312").AutoFilter Field:=1, Criteria1:="1382"
Sheets("Sheet1").Select
Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$1039312").AutoFilter Field:=1, Criteria1:="1382"
Columns("D:D").Select
Sheets("Sheet2").Select
Columns("D:D").Select
HIGHLIGHT DIFFERENCES BETWEEN SHEETS 1 & 2 ON SHEET 1 AND THEN I PUSH A KEY AND RUN AGAIN
ActiveSheet.Range("$A$1:$A$1039312").AutoFilter Field:=1, Criteria1:="1385"
Sheets("Sheet1").Select
Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$1039312").AutoFilter Field:=1, Criteria1:="1385"
Columns("D:D").Select
Sheets("Sheet2").Select
Columns("D:D").Select
Then again highlight and repeat etc all the way down. When complete I will have sheet 1 will all the normal and highlighted missing data.
End Sub
Last edited by a moderator: