How do i copy my friendlist to a new main yahooaccount?

Lookup and copy/paste vba

• QUESTION: I need to create a workbook that will look sales data up by the salesperson's name and return it in a single sheet. I have looked around the internet for a long time, and the best I could come up with still leaves me short. Basically, I want the sheet to operate this way: I input a sales rep's name in the input box, which displays the rep's name in A1 on the report sheet. Then, the workbook loops through the data sheets and returns the data that corresponds to the name without overwriting itself. Right now, with the code below, I have the input box in Module 1, which works fine. The code in Module 2 works, but it takes a long time (up to ten seconds on a dual-core laptop) to return values, it doesn't loop, and it overwrites previous data. Code: Module 1 - Sub Using_InputBox_Method() Dim Response As String ' Run the Input Box. Response = Application.InputBox("Enter salesperson's name:", "Name Entry") ' If not, write the number to the first cell in the first sheet. Worksheets(1).Range("a1").Value = Response End Sub Module 2 - Sub CopyActivations() 'Copy cells of cols A-J from rows containing rep name in 'col E of the active worksheet (source sheet) to cols 'A-J of Detail (destination sheet) Dim DestSheet As Worksheet Set DestSheet = Worksheets("Detail") Dim sRow As Long 'row index on source worksheet Dim dRow As Long 'row index on destination worksheet Dim sCount As Long sCount = 0 dRow = 1 For sRow = 1 To Range("D65536").End(xlUp).Row 'use pattern matching to find "Significant" anywhere in cell If Cells(sRow, "E") Like Sheets("Detail").Range("A1").Value Then sCount = sCount + 1 dRow = dRow + 1 'copy cols A-J Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A") Cells(sRow, "B").Copy Destination:=DestSheet.Cells(dRow, "B") Cells(sRow, "C").Copy Destination:=DestSheet.Cells(dRow, "C") Cells(sRow, "D").Copy Destination:=DestSheet.Cells(dRow, "D") Cells(sRow, "E").Copy Destination:=DestSheet.Cells(dRow, "E") Cells(sRow, "F").Copy Destination:=DestSheet.Cells(dRow, "F") Cells(sRow, "G").Copy Destination:=DestSheet.Cells(dRow, "G") Cells(sRow, "H").Copy Destination:=DestSheet.Cells(dRow, "H") Cells(sRow, "I").Copy Destination:=DestSheet.Cells(dRow, "I") Cells(sRow, "J").Copy Destination:=DestSheet.Cells(dRow, "J") Cells(sRow, "K").Copy Destination:=DestSheet.Cells(dRow, "K") End If Next sRow MsgBox sCount & " rows copied.", vbInformation, "Transfer Done" End Sub ANSWER: Dave, so you want me to add a loop to the code that loops through sheets? It seems like I have seen this code up on this site before - but I don't know who you were working with. Sub CopyActivations() 'Copy cells of cols A-K from rows containing rep name in 'col E of the active worksheet (source sheet) to cols 'A-K of Detail (destination sheet) Dim DestSheet As Worksheet Set DestSheet = Worksheets("Detail") Dim sRow As Long 'row index on source worksheet Dim dRow As Long 'row index on destination worksheet Dim sCount As Long Dim sh As Worksheet sCount = 0 'dRow = 1 dRow = Destsheet.Cells(Destsheet.rows.count,"E").end(xlup).row for each sh in worksheets if sh.Name <> DestSheet.Name then For sRow = 1 To sh.Range("D65536").End(xlUp).Row 'use pattern matching to find "Significant" anywhere in cell If Instr(1, sh.Cells(sRow, "E"), sh.Range("A1").Value, _ vbTextcompare) Then sCount = sCount + 1 dRow = dRow + 1 'copy cols A-J sh.Cells(sRow, "A").Resize(1,11).Copy DestSheet.Cells(dRow, "A").PasteSpecial xlValues DestSheet.Cells(dRow, "A").PasteSpecial xlFormats End If Next sRow next sh MsgBox sCount & " rows copied.", vbInformation, "Transfer Done" End Sub ---------- FOLLOW-UP ---------- QUESTION: You have seen this code on the site before - I have been researching for a while trying to piece this together. Unfortunately for me, I'm a VBA rookie and have no clue what I'm doing. My main priority is for the code to copy and paste the A-K values in all the rows which correspond to the name I enter. I'd like to be able to specify how many blank rows to leave as well, for formatting purposes. These sheets are for a commission statement for salespeople, so I'd like to make it look tidy if I can. As far as looping through the sheets, that would be cool but I don't mind tabbing through and running it manually if I have to. That still beats copy/pasting, which is what has been the process until I decided to improve it. Thanks for your help, D ANSWER: Dave, I gave you the code you asked for. It loops through the sheets. I adjusted the determination of dRow to start below any existing data. I didn't see any other request in your original posting. Thus I am not sure what your followup is all about since I answered you question as I understood it. You didn't have anything to say about blank rows in your previous post and just saying you would like to specify how many blank rows doesn't tell me how you want blank rows applied. Confused. ---------- FOLLOW-UP ---------- QUESTION: I'm getting a compile error: next without for, on line 29. I am sorry I confused you, this is still a work in progress for me. I need to be able to format it in a way that is readable, and I didn't think of that when I first sought your help. If you need to see the sheets themselves, let me know and I will send them to you. D

• Answer:

  corrected code: (untested, but compiles) Test it on a copy of your workbook Sub CopyActivations() 'Copy cells of cols A-K from rows containing rep name in 'col E of the active worksheet (source sheet) to cols 'A-K of Detail (destination sheet) Dim DestSheet As Worksheet Set DestSheet = Worksheets("Detail") Dim sRow As Long 'row index on source worksheet Dim dRow As Long 'row index on destination worksheet Dim sCount As Long Dim sh As Worksheet sCount = 0 'dRow = 1 dRow = DestSheet.Cells(DestSheet.Rows.Count, "E").End(xlUp).Row For Each sh In Worksheets If sh.Name <> DestSheet.Name Then For sRow = 1 To sh.Range("D65536").End(xlUp).Row 'use pattern matching to find "Significant" anywhere in cell If InStr(1, sh.Cells(sRow, "E"), sh.Range("A1").Value, _ vbTextCompare) Then sCount = sCount + 1 dRow = dRow + 1 'copy cols A-J sh.Cells(sRow, "A").Resize(1, 11).Copy DestSheet.Cells(dRow, "A").PasteSpecial xlValues DestSheet.Cells(dRow, "A").PasteSpecial xlFormats End If Next sRow End If Next sh MsgBox sCount & " rows copied.", vbInformation, "Transfer Done" End Sub