I'm trying to collate data from two sheets (sheet1 & 3) , paste it on sheet5 and do some calc. but the macro keeps copying the same row after 2 iterations. Also the find function stops working after 1st iteration. Not sure what to do Sub Macro17()
'
' Macro17 Macro
'
'
Sheets("Sheet1").Select
Range("B3:L3").Select
Do Until Selection.Row = 10000
Selection.Copy
Sheets("Sheet5").Select
ActiveSheet.Paste
Range("F4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Columns("B:F").Select
Range("B:F").Select
dateselect = Selection.Text
Selection.Find(What:=dateselect, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("B167:F260").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet5").Select
Range("M4").Select
ActiveSheet.Paste
Range("R4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-9]"
Range("B4:L4").Select
Selection.Copy
Range("B5:L97").Select
ActiveSheet.Paste
Range("R4").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("R4:R97")
Range("B4").Select
ActiveWindow.SmallScroll Down:=69
Sheets("Sheet1").Select
Range("B3:L3").Offset(1, 0).Select
Loop
'
' Macro17 Macro
'
'
Sheets("Sheet1").Select
Range("B3:L3").Select
Do Until Selection.Row = 10000
Selection.Copy
Sheets("Sheet5").Select
ActiveSheet.Paste
Range("F4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Columns("B:F").Select
Range("B:F").Select
dateselect = Selection.Text
Selection.Find(What:=dateselect, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("B167:F260").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet5").Select
Range("M4").Select
ActiveSheet.Paste
Range("R4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-9]"
Range("B4:L4").Select
Selection.Copy
Range("B5:L97").Select
ActiveSheet.Paste
Range("R4").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("R4:R97")
Range("B4").Select
ActiveWindow.SmallScroll Down:=69
Sheets("Sheet1").Select
Range("B3:L3").Offset(1, 0).Select
Loop