VBA Macro problem- URGENT help

Nitias

New Member
Joined
Jul 18, 2013
Messages
3
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

New Member
Joined
Jul 18, 2013
Messages
3
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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 ???
Code:
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

New Member
Joined
Jul 18, 2013
Messages
3
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 ???
Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,366
Messages
5,601,210
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top