How to copy each row from a worksheet into new workbooks?

Conditional copy

• QUESTION: i need a macro that copy a data based on the change of random generator RANDBETWEEN(0,1) function regardless the number is repeat. example,any action(key in or delete)that will "refresh" random generator,the macro will copy A1 and paste in B2,C2,D2,E2 and so on (same row but different alphabet). ANSWER: ck, in a general module (insert=>module in the Visual Basic Editor) put in this code Sub copydata() Dim r As Range, cell As Range Application.EnableEvents = False With ActiveSheet Set r = Columns(1).SpecialCells(xlFormulas, xlNumbers) If r.Column Application.EnableEvents = True End Sub now right click on the sheet tab back inand select view code in that Sheet module put in code like this: Private Sub Worksheet_Calculate() copydata End Sub Private Sub Worksheet_Change(ByVal Target As Range) copydata End Sub Hope that is what you want. ---------- FOLLOW-UP ---------- QUESTION: i had follow your instruction,an error named "no cells was found"turn out. i am usingexcel,and the sheet named "sheet1" default name. QUESTION: i need macro with conditional copy.if macro found "copythis" in any cell in row B then copy data same column with that spcific cell in from row A. example,found "copythis" in B1 then copy data A1,or found "copythis" in B2 then copy data A2. after copy,paste data in Sheet named summary in row A from left side to right side. ANSWER: ck you say row B and row A, but rows have numbers and columns have letters. I will assume you want to loop through column B starting in row 1 to the last cell with data. If copythis is found in a cell in column B, the corresponding value in column A of the same row is copied to a sheet named summary row 1 and continue to copy going to the right in row 1 of summary. Sub CopyThisMacro() Dim sh As Worksheet, sh1 As Worksheet Dim col As Long, cell As Range, r As Range Set sh = ActiveSheet Set sh1 = Worksheets("Summary") col = 1 Set r = sh.Range("B1", sh.Cells(sh.Rows.Count, "B").End(xlUp)) For Each cell In r If Len(Trim(cell.Text)) > 7 Then If InStr(1, cell, "copythis", vbTextCompare) Then sh1.Cells(1, col).Value = cell.Offset(0, -1).Value col = col + 1 End If End If Next End Sub Test this on a copy of your workbook. ---------- FOLLOW-UP ---------- QUESTION: sorry,i make mistake the question should be this; if macro found "copythis" in any cell in row 2 then copy data with same column with that spcific cell from row 1. example,found "copythis" in B2 then copy data B1,or found "copythis" in A2 then copy data A1. after copy,paste data in Sheet named summary in row 1 from left side to right side.

• Answer:

  ck, I understood you to say you had formulas in column A that produced numbers. this selects all those cells in column A Set r = Columns(1).SpecialCells(xlFormulas, xlNumbers) Apparently that isn't the case so you can try chaning that to Set r = Columns(1).SpecialCells(xlConstants) If that doesn't work, then there I apparently don't understand the situation and you can try someone else. The code I posted worked fine for me. I was using a formula like =trunc(rand()*100+1) in several cells in column A. (I never use randbetween because I can get whatever I want from rand(). ) ck Sub CopyThisMacro1() Dim sh As Worksheet, sh1 As Worksheet Dim col As Long, cell As Range, r As Range Set sh = ActiveSheet Set sh1 = Worksheets("Summary") col = 1 Set r = sh.Range("A2", sh.Cells(2, sh.Columns.count).End(xltoLeft)) For Each cell In r If Len(Trim(cell.Text)) > 7 Then If InStr(1, cell, "copythis", vbTextCompare) Then sh1.Cells(1, col).Value = cell.Offset(-1,0).Value col = col + 1 End If End If Next End Sub Test this on a copy of your workbook until you are satisfied it performs as expected.