Code:
--------------------

'Go back and sort out discounts
Sheets("Temp").Activate

'Find the end of the discounts
Cells.Find(What:="total discounts", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim DiscountEnd As Variant
DiscountEnd = ActiveCell.Row
Range("A1").Select


'Find the Discount start
Cells.Find(What:="discount reason", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim DiscountStart As Variant
DiscountStart = ActiveCell.Row

Dim DiscountRange As Variant
DiscountRange = DiscountEnd - DiscountStart


'Copy the Discount data to move to blank space
Dim disrng As Range
Set disrng = ActiveCell.Offset(0, 0).Resize(DiscountRange, 3)
disrng.Copy
Range("J10").Select
ActiveSheet.Paste

'Copy the discount methods from the Till02 sheet to the Temp Sheet
Sheets("Till02").Select
Range("B5:B30").Select
Selection.Copy
Sheets("Temp").Select
Range("N5").Select
ActiveSheet.Paste




'Set discount reasons in a Copy / pastable format
Worksheets("Temp").Activate
Range("R5").Select
ActiveCell.Formula = "=IF(ISNA(VLOOKUP(N5,Temp!J10:L36,3,FALSE)=TRUE)," """,VLOOKUP(N5,Temp!J10:L36,3,FALSE))"
ActiveCell.Offset(1, 0).Formula = "=IF(ISNA(VLOOKUP(N6,Temp!$J$10:$L$36,3,FALSE)=TRU E),"""",VLOOKUP(N6,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(2, 0).Formula = "=IF(ISNA(VLOOKUP(N7,Temp!$J$10:$L$36,3,FALSE)=TRU E),"""",VLOOKUP(N7,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(3, 0).Formula = "=IF(ISNA(VLOOKUP(N8,Temp!$J$10:$L$36,3,FALSE)=TRU E),"""",VLOOKUP(N8,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(4, 0).Formula = "=IF(ISNA(VLOOKUP(N9,Temp!$J$10:$L$36,3,FALSE)=TRU E),"""",VLOOKUP(N9,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(5, 0).Formula = "=IF(ISNA(VLOOKUP(N10,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N10,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(6, 0).Formula = "=IF(ISNA(VLOOKUP(N11,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N11,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(7, 0).Formula = "=IF(ISNA(VLOOKUP(N12,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N12,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(8, 0).Formula = "=IF(ISNA(VLOOKUP(N13,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N13,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(9, 0).Formula = "=IF(ISNA(VLOOKUP(N14,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N14,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(10, 0).Formula = "=IF(ISNA(VLOOKUP(N15,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N15,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(11, 0).Formula = "=IF(ISNA(VLOOKUP(N16,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N16,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(12, 0).Formula = "=IF(ISNA(VLOOKUP(N17,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N17,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(13, 0).Formula = "=IF(ISNA(VLOOKUP(N18,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N18,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(14, 0).Formula = "=IF(ISNA(VLOOKUP(N19,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N19,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(15, 0).Formula = "=IF(ISNA(VLOOKUP(N20,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N20,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(16, 0).Formula = "=IF(ISNA(VLOOKUP(N21,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N21,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(17, 0).Formula = "=IF(ISNA(VLOOKUP(N22,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N22,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(18, 0).Formula = "=IF(ISNA(VLOOKUP(N23,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N23,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(19, 0).Formula = "=IF(ISNA(VLOOKUP(N24,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N24,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(20, 0).Formula = "=IF(ISNA(VLOOKUP(N25,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N25,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(21, 0).Formula = "=IF(ISNA(VLOOKUP(N26,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N26,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(22, 0).Formula = "=IF(ISNA(VLOOKUP(N27,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N27,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(23, 0).Formula = "=IF(ISNA(VLOOKUP(N28,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N28,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(24, 0).Formula = "=IF(ISNA(VLOOKUP(N29,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N29,Temp!$J$10:$L$36,3,FALSE))"
ActiveCell.Offset(25, 0).Formula = "=IF(ISNA(VLOOKUP(N30,Temp!$J$10:$L$36,3,FALSE)=TR UE),"""",VLOOKUP(N30,Temp!$J$10:$L$36,3,FALSE))"

'Check that we have all the discounts listed in the main sheet
Range("L40").Select
ActiveCell.Formula = "=COUNT(L11:L39)"
Range("R40").Select
ActiveCell.Formula = "=COUNT(R5:R31)"
Range("S40").Select
ActiveCell.Formula = "=IF(L40<R40,1,0)"



If ActiveCell.Value < 0 Then
MsgBox "New Discount Method Found"
Else
End If




'Copy discount values to tillsheet

Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(0, 1).Value = Worksheets("Temp").Cells(5, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(1, 1).Value = Worksheets("Temp").Cells(6, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(2, 1).Value = Worksheets("Temp").Cells(7, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(3, 1).Value = Worksheets("Temp").Cells(8, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(4, 1).Value = Worksheets("Temp").Cells(9, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(5, 1).Value = Worksheets("Temp").Cells(10, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(6, 1).Value = Worksheets("Temp").Cells(11, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(7, 1).Value = Worksheets("Temp").Cells(12, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(8, 1).Value = Worksheets("Temp").Cells(13, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(9, 1).Value = Worksheets("Temp").Cells(14, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(11, 1).Value = Worksheets("Temp").Cells(15, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(12, 1).Value = Worksheets("Temp").Cells(16, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(13, 1).Value = Worksheets("Temp").Cells(17, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(14, 1).Value = Worksheets("Temp").Cells(18, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(15, 1).Value = Worksheets("Temp").Cells(19, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(16, 1).Value = Worksheets("Temp").Cells(20, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(17, 1).Value = Worksheets("Temp").Cells(21, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(18, 1).Value = Worksheets("Temp").Cells(22, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(19, 1).Value = Worksheets("Temp").Cells(23, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(20, 1).Value = Worksheets("Temp").Cells(24, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(21, 1).Value = Worksheets("Temp").Cells(25, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(22, 1).Value = Worksheets("Temp").Cells(26, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(23, 1).Value = Worksheets("Temp").Cells(27, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(24, 1).Value = Worksheets("Temp").Cells(28, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(25, 1).Value = Worksheets("Temp").Cells(29, 18).Value
Worksheets("Till0" & TillNumber).Cells(Discounts, CapDateCol).Offset(26, 1).Value = Worksheets("Temp").Cells(30, 18).Value

'Finish up by returning to sheet and prompting for finish
Sheets("Till0" & TillNumber).Select
Range("A1").Select
MsgBox "Cap Extraction completed for" & CapDate

End Sub
--------------------


--
drucey
------------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=539306