VBA Macro problem- URGENT help

[Nitias]

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

 

[Michael M]

Is it always going to be exactly 1000 rows or is it B3 down to the last row in column "B" ?
AND
Is it the same on both sheets 1 & 3 ??

 

[Nitias]

Is it always going to be exactly 1000 rows or is it B3 down to the last row in column "B" ?
AND
Is it the same on both sheets 1 & 3 ??

No , it may change . Column B will be better . But I dint know how to put it. So put 10000.
Info: I'm a beginner to VB macro. So please bear with me

 

[Michael M]

Ok, as a starting point this will copy the data from B3 all the way down to the last row and across to column L on Sheet 1 to Sheet5
But what are you actually trying to do with the data on sheet 3 ???

Sub Macro17()
Dim lr As Long, r As Long, lr2 As Long
lr2 = Sheets("Sheet5").Cells(Rows.Count, 2).End(xlUp).Row
lr = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Sheet1").Range("B3:L" & lr).Copy Destination:=Sheets("Sheet5").Range("A" & lr2 + 1)
End Sub

 

[Nitias]

Ok, as a starting point this will copy the data from B3 all the way down to the last row and across to column L on Sheet 1 to Sheet5
But what are you actually trying to do with the data on sheet 3 ???

Sub Macro17()
Dim lr As Long, r As Long, lr2 As Long
lr2 = Sheets("Sheet5").Cells(Rows.Count, 2).End(xlUp).Row
lr = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Sheet1").Range("B3:L" & lr).Copy Destination:=Sheets("Sheet5").Range("A" & lr2 + 1)
End Sub

Thanks Michael
Now the F column in Sheet 1 (which we have copied to sheet5) has a date. I need to search the date in sheet 3 and copy the next four columns to sheet 5. i.e If the data in sheet 3 is in B3:F500 and my search resullt gives me the date in cell B10, I want to copy C10:F500 and paste it to the corresponding row in Sheet 5.

Info: For my requirement I cannot copy all rows in sheet 1 at one go. I gotto copy it one at a time.

 

[Michael M]

Info: For my requirement I cannot copy all rows in sheet 1 at one go. I gotto copy it one at a time.

Why do you have to copy one row at a time ??, thats very inefficient