I'm looking for a way to undo the below sub. Currently, I have to go into each of the 7 sheets that the sub updates and I have to clear the contents that the last sub provided. The sub also keeps a running update, so it just adds to each sheet, not replaces. I would like the undo sub to undo just the last run sub, and not undo all previous. Any help or advice is greatly appreciated!
Sub If_Red_Copy_to_ALLmissed() Dim icell As Range, myRange As Range Dim endcell As Range Dim index As Long Set myRange = Union(Range("D3:d80"), Range("h3:h80"), Range("l3:l80"), Range("p3:p80"), Range("t3:t80")) For Each icell In myRange If icell.Interior.Color = RGB(255, 204, 0) Then With Sheets("IDO_Supplier") indexO = .Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 .Cells(indexO, 6).Value = icell.Value .Cells(indexO, 3).Value = icell.Offset(0, -2).Value .Cells(indexO, 4).Value = icell.Offset(0, -1).Value .Cells(indexO, 5).Value = "IDO/Supplier" .Cells(indexO, 11).Value = Now() .Cells(indexO, 2).Formula = "=WEEKNUM(NOW())-1" .Cells(indexO, 7).Formula = "=IF(AND($B" & indexO & ">=1, $B" & indexO & "<=4), ""January:"", IF(AND($B" & indexO & ">=5, $B" & indexO & "<=8), ""February"", IF(AND($B" & indexO & ">=9, $B" & indexO & "<=13), ""March"", IF(AND($B" & indexO & ">=14, $B" & indexO & "<=17), ""April"", IF(AND($B" & indexO & ">=18, $B" & indexO & "<=21), ""May"", IF(AND($B" & indexO & ">=22, $B" & indexO & "<=26), ""June"", IF(AND($B" & indexO & ">=27, $B" & indexO & "<=30), ""July"", IF(AND($B" & indexO & ">=31, $B" & indexO & "<=34), ""August"", IF(AND($B" & indexO & ">=35, $B" & indexO & "<=39), ""September"", IF(AND($B" & indexO & ">=40, $B" & indexO & "<=43), ""October"", IF(AND($B" & indexO & ">=44, $B" & indexO & "<=47), ""November"", IF(AND($B" & indexO & ">=48, $B" & indexO & "<=52), ""December"", """"))))))))))))" .Cells(indexO, 9).Formula = "=VLOOKUP($C" & indexO & ", enginelist!$A$2:$B$150, 2, FALSE)" .Cells(indexO, 10).Formula = "=VLOOKUP($C" & indexO & ", enginelist!$A$2:$C$150, 3, FALSE)" .Cells(indexO, 8).Formula = "=IF(ISNA(MATCH(D" & indexO & ",Sheet1!$C$3:$C$80,0)),"""",Sheet1!$C$1) & IF(ISNA(MATCH(D" & indexO & ",Sheet1!$G$3:$G$80,0)),"""",Sheet1!$G$1) & IF(ISNA(MATCH(D" & indexO & ",Sheet1!$K$3:$K$80,0)),"""",Sheet1!$J$1) & IF(ISNA(MATCH(D" & indexO & ",Sheet1!$O$3:$O$80,0)),"""",Sheet1!$N$1) & IF(ISNA(MATCH(D" & indexO & ",Sheet1!$S$3:$S$80,0)),"""",Sheet1!$R$1)" index = indexO + 1 End With End If If icell.Interior.Color = RGB(255, 0, 255) Then With Sheets("Clear_Assy") indexPi = .Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 .Cells(indexPi, 6).Value = icell.Value .Cells(indexPi, 3).Value = icell.Offset(0, -2).Value .Cells(indexPi, 4).Value = icell.Offset(0, -1).Value .Cells(indexPi, 5).Value = "Clear-Assy/Capacity" .Cells(indexPi, 11).Value = Now() .Cells(indexPi, 2).Formula = "=WEEKNUM(NOW())-1" .Cells(indexPi, 7).Formula = "=IF(AND($B" & indexPi & ">=1, $B" & indexPi & "<=4), ""January:"", IF(AND($B" & indexPi & ">=5, $B" & indexPi & "<=8), ""February"", IF(AND($B" & indexPi & ">=9, $B" & indexPi & "<=13), ""March"", IF(AND($B" & indexPi & ">=14, $B" & indexPi & "<=17), ""April"", IF(AND($B" & indexPi & ">=18, $B" & indexPi & "<=21), ""May"", IF(AND($B" & indexPi & ">=22, $B" & indexPi & "<=26), ""June"", IF(AND($B" & indexPi & ">=27, $B" & indexPi & "<=30), ""July"", IF(AND($B" & indexPi & ">=31, $B" & indexPi & "<=34), ""August"", IF(AND($B" & indexPi & ">=35, $B" & indexPi & "<=39), ""September"", IF(AND($B" & indexPi & ">=40, $B" & indexPi & "<=43), ""October"", IF(AND($B" & indexPi & ">=44, $B" & indexPi & "<=47), ""November"", IF(AND($B" & indexPi & ">=48, $B" & indexPi & "<=52), ""December"", """"))))))))))))" .Cells(indexPi, 9).Formula = "=VLOOKUP($C" & indexPi & ", enginelist!$A$2:$B$150, 2, FALSE)" .Cells(indexPi, 10).Formula = "=VLOOKUP($C" & indexPi & ", enginelist!$A$2:$C$150, 3, FALSE)" .Cells(indexPi, 8).Formula = "=IF(ISNA(MATCH(D" & indexPi & ",Sheet1!$C$3:$C$80,0)),"""",Sheet1!$C$1) & IF(ISNA(MATCH(D" & indexPi & ",Sheet1!$G$3:$G$80,0)),"""",Sheet1!$G$1) & IF(ISNA(MATCH(D" & indexPi & ",Sheet1!$K$3:$K$80,0)),"""",Sheet1!$J$1) & IF(ISNA(MATCH(D" & indexPi & ",Sheet1!$O$3:$O$80,0)),"""",Sheet1!$N$1) & IF(ISNA(MATCH(D" & indexPi & ",Sheet1!$S$3:$S$80,0)),"""",Sheet1!$R$1)" index = indexPi + 1 End With End If If icell.Interior.Color = RGB(178, 161, 199) Then With Sheets("reject_fail") indexPu = .Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 .Cells(indexPu, 6).Value = icell.Value .Cells(indexPu, 3).Value = icell.Offset(0, -2).Value .Cells(indexPu, 4).Value = icell.Offset(0, -1).Value .Cells(indexPu, 5).Value = "Test Fail/Engine Rejection" .Cells(indexPu, 11).Value = Now() .Cells(indexPu, 2).Formula = "=WEEKNUM(NOW())-1" .Cells(indexPu, 7).Formula = "=IF(AND($B" & indexPu & ">=1, $B" & indexPu & "<=4), ""January:"", IF(AND($B" & indexPu & ">=5, $B" & indexPu & "<=8), ""February"", IF(AND($B" & indexPu & ">=9, $B" & indexPu & "<=13), ""March"", IF(AND($B" & indexPu & ">=14, $B" & indexPu & "<=17), ""April"", IF(AND($B" & indexPu & ">=18, $B" & indexPu & "<=21), ""May"", IF(AND($B" & indexPu & ">=22, $B" & indexPu & "<=26), ""June"", IF(AND($B" & indexPu & ">=27, $B" & indexPu & "<=30), ""July"", IF(AND($B" & indexPu & ">=31, $B" & indexPu & "<=34), ""August"", IF(AND($B" & indexPu & ">=35, $B" & indexPu & "<=39), ""September"", IF(AND($B" & indexPu & ">=40, $B" & indexPu & "<=43), ""October"", IF(AND($B" & indexPu & ">=44, $B" & indexPu & "<=47), ""November"", IF(AND($B" & indexPu & ">=48, $B" & indexPu & "<=52), ""December"", """"))))))))))))" .Cells(indexPu, 9).Formula = "=VLOOKUP($C" & indexPu & ", enginelist!$A$2:$B$150, 2, FALSE)" .Cells(indexPu, 10).Formula = "=VLOOKUP($C" & indexPu & ", enginelist!$A$2:$C$150, 3, FALSE)" .Cells(indexPu, 8).Formula = "=IF(ISNA(MATCH(D" & indexPu & ",Sheet1!$C$3:$C$80,0)),"""",Sheet1!$C$1) & IF(ISNA(MATCH(D" & indexPu & ",Sheet1!$G$3:$G$80,0)),"""",Sheet1!$G$1) & IF(ISNA(MATCH(D" & indexPu & ",Sheet1!$K$3:$K$80,0)),"""",Sheet1!$J$1) & IF(ISNA(MATCH(D" & indexPu & ",Sheet1!$O$3:$O$80,0)),"""",Sheet1!$N$1) & IF(ISNA(MATCH(D" & indexPu & ",Sheet1!$S$3:$S$80,0)),"""",Sheet1!$R$1)" index = indexPu + 1 End With
End If If icell.Interior.Color = RGB(0, 176, 240) Then With Sheets("PartQuality") indexB = .Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 .Cells(indexB, 6).Value = icell.Value .Cells(indexB, 3).Value = icell.Offset(0, -2).Value .Cells(indexB, 4).Value = icell.Offset(0, -1).Value .Cells(indexB, 5).Value = "Part Quality" .Cells(indexB, 11).Value = Now() .Cells(indexB, 2).Formula = "=WEEKNUM(NOW())-1" .Cells(indexB, 7).Formula = "=IF(AND($B" & indexB & ">=1, $B" & indexB & "<=4), ""January:"", IF(AND($B" & indexB & ">=5, $B" & indexB & "<=8), ""February"", IF(AND($B" & indexB & ">=9, $B" & indexB & "<=13), ""March"", IF(AND($B" & indexB & ">=14, $B" & indexB & "<=17), ""April"", IF(AND($B" & indexB & ">=18, $B" & indexB & "<=21), ""May"", IF(AND($B" & indexB & ">=22, $B" & indexB & "<=26), ""June"", IF(AND($B" & indexB & ">=27, $B" & indexB & "<=30), ""July"", IF(AND($B" & indexB & ">=31, $B" & indexB & "<=34), ""August"", IF(AND($B" & indexB & ">=35, $B" & indexB & "<=39), ""September"", IF(AND($B" & indexB & ">=40, $B" & indexB & "<=43), ""October"", IF(AND($B" & indexB & ">=44, $B" & indexB & "<=47), ""November"", IF(AND($B" & indexB & ">=48, $B" & indexB & "<=52), ""December"", """"))))))))))))" .Cells(indexB, 9).Formula = "=VLOOKUP($C" & indexB & ", enginelist!$A$2:$B$150, 2, FALSE)" .Cells(indexB, 10).Formula = "=VLOOKUP($C" & indexB & ", enginelist!$A$2:$C$150, 3, FALSE)" .Cells(indexB, 8).Formula = "=IF(ISNA(MATCH(D" & indexB & ",Sheet1!$C$3:$C$80,0)),"""",Sheet1!$C$1) & IF(ISNA(MATCH(D" & indexB & ",Sheet1!$G$3:$G$80,0)),"""",Sheet1!$G$1) & IF(ISNA(MATCH(D" & indexB & ",Sheet1!$K$3:$K$80,0)),"""",Sheet1!$J$1) & IF(ISNA(MATCH(D" & indexB & ",Sheet1!$O$3:$O$80,0)),"""",Sheet1!$N$1) & IF(ISNA(MATCH(D" & indexB & ",Sheet1!$S$3:$S$80,0)),"""",Sheet1!$R$1)" index = indexB + 1 End With End If If icell.Interior.Color = RGB(0, 255, 0) Then With Sheets("HFbackend") indexG = .Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 .Cells(indexG, 6).Value = icell.Value .Cells(indexG, 3).Value = icell.Offset(0, -2).Value .Cells(indexG, 4).Value = icell.Offset(0, -1).Value .Cells(indexG, 5).Value = "HFbackend" .Cells(indexG, 11).Value = Now() .Cells(indexG, 2).Formula = "=WEEKNUM(NOW())-1" .Cells(indexG, 7).Formula = "=IF(AND($B" & indexG & ">=1, $B" & indexG & "<=4), ""January:"", IF(AND($B" & indexG & ">=5, $B" & indexG & "<=8), ""February"", IF(AND($B" & indexG & ">=9, $B" & indexG & "<=13), ""March"", IF(AND($B" & indexG & ">=14, $B" & indexG & "<=17), ""April"", IF(AND($B" & indexG & ">=18, $B" & indexG & "<=21), ""May"", IF(AND($B" & indexG & ">=22, $B" & indexG & "<=26), ""June"", IF(AND($B" & indexG & ">=27, $B" & indexG & "<=30), ""July"", IF(AND($B" & indexG & ">=31, $B" & indexG & "<=34), ""August"", IF(AND($B" & indexG & ">=35, $B" & indexG & "<=39), ""September"", IF(AND($B" & indexG & ">=40, $B" & indexG & "<=43), ""October"", IF(AND($B" & indexG & ">=44, $B" & indexG & "<=47), ""November"", IF(AND($B" & indexG & ">=48, $B" & indexG & "<=52), ""December"", """"))))))))))))" .Cells(indexG, 9).Formula = "=VLOOKUP($C" & indexG & ", enginelist!$A$2:$B$150, 2, FALSE)" .Cells(indexG, 10).Formula = "=VLOOKUP($C" & indexG & ", enginelist!$A$2:$C$150, 3, FALSE)" .Cells(indexG, 8).Formula = "=IF(ISNA(MATCH(D" & indexG & ",Sheet1!$C$3:$C$80,0)),"""",Sheet1!$C$1) & IF(ISNA(MATCH(D" & indexG & ",Sheet1!$G$3:$G$80,0)),"""",Sheet1!$G$1) & IF(ISNA(MATCH(D" & indexG & ",Sheet1!$K$3:$K$80,0)),"""",Sheet1!$J$1) & IF(ISNA(MATCH(D" & indexG & ",Sheet1!$O$3:$O$80,0)),"""",Sheet1!$N$1) & IF(ISNA(MATCH(D" & indexG & ",Sheet1!$S$3:$S$80,0)),"""",Sheet1!$R$1)" index = indexG + 1 End With End If If icell.Interior.Color = RGB(255, 255, 0) Then With Sheets("Cleared_Too_Late") indexY = .Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 .Cells(indexY, 6).Value = icell.Value .Cells(indexY, 3).Value = icell.Offset(0, -2).Value .Cells(indexY, 4).Value = icell.Offset(0, -1).Value .Cells(indexY, 5).Value = "Cleared Too Late to Ship" .Cells(indexY, 11).Value = Now() .Cells(indexY, 2).Formula = "=WEEKNUM(NOW())-1" .Cells(indexY, 7).Formula = "=IF(AND($B" & indexY & ">=1, $B" & indexY & "<=4), ""January:"", IF(AND($B" & indexY & ">=5, $B" & indexY & "<=8), ""February"", IF(AND($B" & indexY & ">=9, $B" & indexY & "<=13), ""March"", IF(AND($B" & indexY & ">=14, $B" & indexY & "<=17), ""April"", IF(AND($B" & indexY & ">=18, $B" & indexY & "<=21), ""May"", IF(AND($B" & indexY & ">=22, $B" & indexY & "<=26), ""June"", IF(AND($B" & indexY & ">=27, $B" & indexY & "<=30), ""July"", IF(AND($B" & indexY & ">=31, $B" & indexY & "<=34), ""August"", IF(AND($B" & indexY & ">=35, $B" & indexY & "<=39), ""September"", IF(AND($B" & indexY & ">=40, $B" & indexY & "<=43), ""October"", IF(AND($B" & indexY & ">=44, $B" & indexY & "<=47), ""November"", IF(AND($B" & indexY & ">=48, $B" & indexY & "<=52), ""December"", """"))))))))))))" .Cells(indexY, 9).Formula = "=VLOOKUP($C" & indexY & ", enginelist!$A$2:$B$150, 2, FALSE)" .Cells(indexY, 10).Formula = "=VLOOKUP($C" & indexY & ", enginelist!$A$2:$C$150, 3, FALSE)" .Cells(indexY, 8).Formula = "=IF(ISNA(MATCH(D" & indexY & ",Sheet1!$C$3:$C$80,0)),"""",Sheet1!$C$1) & IF(ISNA(MATCH(D" & indexY & ",Sheet1!$G$3:$G$80,0)),"""",Sheet1!$G$1) & IF(ISNA(MATCH(D" & indexY & ",Sheet1!$K$3:$K$80,0)),"""",Sheet1!$J$1) & IF(ISNA(MATCH(D" & indexY & ",Sheet1!$O$3:$O$80,0)),"""",Sheet1!$N$1) & IF(ISNA(MATCH(D" & indexY & ",Sheet1!$S$3:$S$80,0)),"""",Sheet1!$R$1)" index = indexY + 1 End With End If Next icell Set myRange = Union(Range("B3:B80"), Range("F3:F80"), Range("J3:J80"), Range("N3:N80"), Range("R3:R80"))
For Each icell In myRange If icell.Interior.Color = RGB(255, 0, 0) Then With Sheets("ALLmissed") index = .Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 .Cells(index, 3).Value = icell.Value .Cells(index, 4).Value = icell.Offset(0, 1).Value .Cells(index, 6).Value = icell.Offset(0, 2).Value .Cells(index, 11).Value = Now() .Cells(index, 1).Formula = "=" & index & "-1" .Cells(index, 2).Formula = "=WEEKNUM(NOW())-1" .Cells(index, 5).Formula = "=IFERROR( VLOOKUP($D" & index & ", IDO_Supplier!$D:$E, 2, FALSE), IFERROR(VLOOKUP($D" & index & ", Clear_Assy!$D:$E, 2, FALSE), IFERROR(VLOOKUP($D" & index & ", reject_fail!$D:$E, 2, FALSE), IFERROR(VLOOKUP($D" & index & ", PartQuality!$D:$E, 2, FALSE), IFERROR(VLOOKUP($D" & index & ", HFbackend!$D:$E, 2, FALSE),IFERROR(VLOOKUP($D" & index & ", Cleared_Too_Late!$D:$E, 2, FALSE), """"))))))" .Cells(index, 7).Formula = "=IF(AND($B" & index & ">=1, $B" & index & "<=4), ""January"", IF(AND($B" & index & ">=5, $B" & index & "<=8), ""February"", IF(AND($B" & index & ">=9, $B" & index & "<=13), ""March"", IF(AND($B" & index & ">=14, $B" & index & "<=17), ""April"", IF(AND($B" & index & ">=18, $B" & index & "<=21), ""May"", IF(AND($B" & index & ">=22, $B" & index & "<=26), ""June"", IF(AND($B" & index & ">=27, $B" & index & "<=30), ""July"", IF(AND($B" & index & ">=31, $B" & index & "<=34), ""August"", IF(AND($B" & index & ">=35, $B" & index & "<=39), ""September"", IF(AND($B" & index & ">=40, $B" & index & "<=43), ""October"", IF(AND($B" & index & ">=44, $B" & index & "<=47), ""November"", IF(AND($B" & index & ">=48, $B" & index & "<=52), ""December"", """"))))))))))))" .Cells(index, 9).Formula = "=VLOOKUP($C" & index & ", enginelist!$A$2:$B$150, 2, FALSE)" .Cells(index, 10).Formula = "=VLOOKUP($C" & index & ", enginelist!$A$2:$C$150, 3, FALSE)" .Cells(index, 8).Formula = "=IF(ISNA(MATCH(D" & index & ",Sheet1!$C$3:$C$80,0)),"""",Sheet1!$C$1) & IF(ISNA(MATCH(D" & index & ",Sheet1!$G$3:$G$80,0)),"""",Sheet1!$G$1) & IF(ISNA(MATCH(D" & index & ",Sheet1!$K$3:$K$80,0)),"""",Sheet1!$J$1) & IF(ISNA(MATCH(D" & index & ",Sheet1!$O$3:$O$80,0)),"""",Sheet1!$N$1) & IF(ISNA(MATCH(D" & index & ",Sheet1!$S$3:$S$80,0)),"""",Sheet1!$R$1)" index = index + 1 End With End If Next icell Set myRange = Union(Range("C3:C80"), Range("G3:G80"), Range("K3:K80"), Range("O3:O80"), Range("S3:S80")) For Each icell In myRange If icell.Interior.Color = RGB(255, 0, 0) Then With Sheets("ALLmissed") index = .Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 .Cells(index, 4).Value = icell.Value .Cells(index, 3).Value = icell.Offset(0, -1).Value .Cells(index, 6).Value = icell.Offset(0, 1).Value .Cells(index, 11).Value = Now() .Cells(index, 1).Formula = "=" & index & "-1" .Cells(index, 2).Formula = "=WEEKNUM(NOW())-1" .Cells(index, 5).Formula = "=IFERROR( VLOOKUP($D" & index & ", IDO_Supplier!$D:$E, 2, FALSE), IFERROR(VLOOKUP($D" & index & ", Clear_Assy!$D:$E, 2, FALSE), IFERROR(VLOOKUP($D" & index & ", reject_fail!$D:$E, 2, FALSE), IFERROR(VLOOKUP($D" & index & ", PartQuality!$D:$E, 2, FALSE), IFERROR(VLOOKUP($D" & index & ", HFbackend!$D:$E, 2, FALSE), """")))))" .Cells(index, 7).Formula = "=IF(AND($B" & index & ">=1, $B" & index & "<=4), ""January"", IF(AND($B" & index & ">=5, $B" & index & "<=8), ""February"", IF(AND($B" & index & ">=9, $B" & index & "<=13), ""March"", IF(AND($B" & index & ">=14, $B" & index & "<=17), ""April"", IF(AND($B" & index & ">=18, $B" & index & "<=21), ""May"", IF(AND($B" & index & ">=22, $B" & index & "<=26), ""June"", IF(AND($B" & index & ">=27, $B" & index & "<=30), ""July"", IF(AND($B" & index & ">=31, $B" & index & "<=34), ""August"", IF(AND($B" & index & ">=35, $B" & index & "<=39), ""September"", IF(AND($B" & index & ">=40, $B" & index & "<=43), ""October"", IF(AND($B" & index & ">=44, $B" & index & "<=47), ""November"", IF(AND($B" & index & ">=48, $B" & index & "<=52), ""December"", """"))))))))))))" .Cells(index, 9).Formula = "=VLOOKUP($C" & index & ", enginelist!$A$2:$B$150, 2, FALSE)" .Cells(index, 10).Formula = "=VLOOKUP($C" & index & ", enginelist!$A$2:$C$150, 3, FALSE)" .Cells(index, 8).Formula = "=IF(ISNA(MATCH(D" & index & ",Sheet1!$C$3:$C$80,0)),"""",Sheet1!$C$1) & IF(ISNA(MATCH(D" & index & ",Sheet1!$G$3:$G$80,0)),"""",Sheet1!$G$1) & IF(ISNA(MATCH(D" & index & ",Sheet1!$K$3:$K$80,0)),"""",Sheet1!$J$1) & IF(ISNA(MATCH(D" & index & ",Sheet1!$O$3:$O$80,0)),"""",Sheet1!$N$1) & IF(ISNA(MATCH(D" & index & ",Sheet1!$S$3:$S$80,0)),"""",Sheet1!$R$1)" index = index + 1 End With End If Next icell End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks