How to paste a line at a time?

Copy and paste usin vba

• QUESTION: I had to post a new question because I used too many follow ups. To continue my problem: Your very first code copied the last two rows with data correctly from the source file and pasted it in the correct destination file. The problem is that I would like it to be pasted in to cells that have a default value of 0. I will email you the destination spreadsheet to give you a better idea of what I mean. If you would be so kind to look at the tab with the number 7. Your very first code would paste the correct data in rows 51 and 52. I would like it to be pasted in rows 32 and 33 and then, when I run the macro again, in row 34 and 35. With your last code I received the run time error message 438, Object doesn't support this property or method with the following line highlighted: Set r2 = wb2.Range("B1", wb2.Cells(1, 2).End(xlDown)) I do not understand this error meesage. Bert Schenke, Swansea, Wales UK. ANSWER: Bert, my bad: Private Sub Worksheet_Change(ByVal Target As Range) Dim wb As Workbook Dim wb1 As Workbook Dim wb2 As Workbook Dim sh2 as worksheet, r2 as range, cell as range Dim r3 as Range If Not Intersect(Target, Range("n2")) Is Nothing Then For Each wb In Application.Workbooks If wb.Name Like "*Book MK*" Then set wb1 = wb end if If wb.Name Like "*Book SW*" Then set wb2 = wb end if Next set sh2 = wb2.Activesheet set r2 = sh2.Range("B1",sh2.cells(1,2).End(xldown)) for each cell in r2 if cell.value = 0 then set r3 = cell exit for end if Next ' the next line should refer to the last cell in column B with ' a value and the blank cell below resized to 21 columns wide wb1.ActiveSheet.Cells(Rows.Count, "B") _ .End(xlUp).Offset(-1).Resize(2, 21).Copy ' wb2.ActiveSheet.Range("B" & Rows.Count) _ ' .End(xlUp)(1).PasteSpecial Paste:=xlPasteValues r3.pasteSpecial paste:=xlPasteValues End if End Sub Go ahead and send it. ---------- FOLLOW-UP ---------- QUESTION: Mr. Ogilvy, you're just unbelievable. You have an answer to every question I ask and come up with a solution. Now I understand whuy they call you the expert. Your code works great, but I came across an issue that I should have thought of before. I can only blame myself and ask you if there is a solution. I will email you the spreadsheet again and then you will see on sheet 7 what I mean. It is pasting the data on the first and second row it finds with a 0 on the spreadsheet, which is in the case of sheet 7 row 3 and 4. I would like to know if it is possible that the data is going to be pasted below the existing data on sheet 7, which are rows 32 and 33, and then after the macro is run again row 34 and 35. I do apologize for all the confusion I am causing, for not asking you the correct questions the first time around, but I am only a beginner with this and did not expect the excellent answers you give me and did not think of the problems I came across with. Bert. ANSWER: Bert, I would guess you want to start at row 50 and search up the column B until you find the first non-zero value and write in the next row down. Private Sub Worksheet_Change(ByVal Target As Range) Dim wb As Workbook Dim wb1 As Workbook Dim wb2 As Workbook Dim sh2 as worksheet, r2 as range, cell as range Dim r3 as Range If Not Intersect(Target, Range("n2")) Is Nothing Then For Each wb In Application.Workbooks If wb.Name Like "*Book MK*" Then set wb1 = wb end if If wb.Name Like "*Book SW*" Then set wb2 = wb end if Next set sh2 = wb2.Activesheet set r2 = sh2.Range("B50") if r2 <> 0 then msgbox "Out of room, exiting" exit sub End if do while r2 = 0 and r2.row > 2 set r2 = r2.offset(-1,0) loop set r2 = r2.offset(1,0) ' the next line should refer to the last cell in column B with ' a value and the blank cell below resized to 21 columns wide wb1.ActiveSheet.Cells(Rows.Count, "B") _ .End(xlUp).Offset(-1).Resize(2, 21).Copy ' wb2.ActiveSheet.Range("B" & Rows.Count) _ ' .End(xlUp)(1).PasteSpecial Paste:=xlPasteValues r3.pasteSpecial paste:=xlPasteValues End if End Sub I don't know about your source data, but it could be set up to fine a specific row in the sheet based on the value in column A. It seems imprecise this way given your rows have a time value on the left. ---------- FOLLOW-UP ---------- QUESTION: Ijust can not believe that you have an answer for anything I ask you. I have not tried above code yet, but I have all the confidence that it will work. Column A of the source data is the same as that one of the sheet that it needs to be pasted in. Both have a time value in column A. As an example, the data in the row with 12:00 hours in Column A of the source Book SW needs to be pasted in the row with 12:00 hours in Column A of the receiving workbook named *Book MK". I understand the code for that would be easier?

• Answer:

  Bert, I have updated the code (and tested it) to match on the time in column A. And I realize I had gotten my self confused on what was being copied. I see it copies the last two lines of data - so I feel better about that now. Private Sub Worksheet_Change(ByVal Target As Range) Dim wb As Workbook Dim wb1 As Workbook Dim wb2 As Workbook Dim sh2 As Worksheet, r2 As Range, cell As Range Dim r3 As Range If Not Intersect(Target, Range("n2")) Is Nothing Then For Each wb In Application.Workbooks If wb.Name Like "*Book MK*" Then Set wb1 = wb End If If wb.Name Like "*Book SW*" Then Set wb2 = wb End If Next Set sh1 = wb1.ActiveSheet Set r1 = sh1.Cells(Rows.Count, "B") _ .End(xlUp).Offset(-1, -1) Set sh2 = wb2.ActiveSheet Set r2 = sh2.Range("A3:A50") For Each cell In r2 If Abs(r1.Value - cell.Value) Next If r3 Is Nothing Then MsgBox "Can't find the time: " & Format(r1, "hh:mm") Exit Sub End If r1.Offset(0, 1).Resize(2, 21).Copy r3.PasteSpecial Paste:=xlPasteValues End If End Sub