I'm trying to copy cells in SheetA to SheetB but they are split across two or more rows. I need to detect a null (or empty) cell in sheet A and then move down one line to a NOT NULL cell, copy it, paste it into a new record in Sheet B and so on until all Sheet A data is reordered into Sheet B records. Its messy because the source is from a web statistics table that was imported. I cannot get the loop structure right and keep copying over the 1st record I transfer, or copy NULL data instead of moving to a NOT NULL row in Sheet A. Is there a straight forward method for doing this? data is in the form (not all on one row): 03/02/2009 team_a team_b 1-0 10/02/2009 team_a team_b 2-2 15/02/2009 team_a team_b 3-1 -and so on for 100's of rows. sometimes there is more than one empty row between records too. Any help would be much appreciated

Hi, Are the elements of each record separated consistently, apart from the number of rows between records. Are the date, the two team names and the result in separate cells (i.e. 3 cells), or is all this data in one cell. If all three elements are in one cell, do the team names have spaces in them, or is there any consistent separator between elements. Also if all the elements are in one cell, are the dates always a consistent length, for example does this date 03/02/2009 ever appear as 3/2/2009. Finally, if the elements are not in one cell, do they sometimes appear on different rows and sometimes in consecutive cells on one row. I wasn't sure if your example was showing that, or whether it was just the way it happened to display. Please provide the additional information, and we should be able to provide a macro to do what you want Regards

Apologies if it was clear as mud. Each field contains one data item only, none are merged. i.e. if a single record was comma delimited it would appear as: 02/03/2009, team_a, team_b, 1-0 -so, not on a single line, but staggered as above. Therefore to parse each record in sheet1 I need to copy date, paste into sheet2, then move down a line in sheet1, copy team_a, paste into sheet2, etc etc until the whole Sheet1 record is copied on a single line into sheet2

Hi, If I have the source data correct, this code should put each game on one line. Example source data: A B C 1 03/02/09 2 Team a Team b 1-1 3 4 10/02/09 5 Team c Team d 1-0 6 7 15/02/09 8 Team e Team f 2-2 9 10 11 16/02/09 12 Team g Team h 1-1 Output: A B C D 1 Date Team 1 Team 2 Score 2 03/02/09 Team a Team b 1-1 3 10/02/09 Team c Team d 1-0 4 15/02/09 Team e Team f 2-2 5 16/02/09 Team g Team h 1-1 For the example the workbook had two worksheets names "Source" and "Final" The code was placed in a standard Module Option Explicit Private Sub SortTeams() Dim rngCell As Range Dim intDest As Integer Dim intEndCount As Integer On Error GoTo ErrHnd 'set end counter - if we get 10 empty rows - stop intEndCount = 10 'set destination row counter intDest = 1 With ActiveWorkbook For Each rngCell In .Worksheets("Source").Range("A:A").Cells() If IsEmpty(rngCell) Then 'count empty rows intEndCount = intEndCount - 1 If intEndCount = 0 Then Exit For Else 'not empty 'test if date If IsDate(rngCell.Value) Then 'move to next destination row intDest = intDest + 1 'move date to column 1 rngCell.Copy Destination:=.Worksheets("Final").Cells(intDest, 1) Else 'not a date so move three cells to columns 2 to 4 .Worksheets("Source").Range(Cells(rngCell.Row, 1), Cells(rngCell.Row, 3)).Copy _ Destination:=.Worksheets("Final").Cells(intDest, 2) End If 'reset end counter intEndCount = 10 End If Next rngCell End With Exit Sub 'error handler ErrHnd: Err.Clear End Sub This code does not address adding more results on the end of the table. You could change intDest based on finding the last used cell in column A of the "Final" worksheet, each time the code is run. The code can be linked to a toolbar button or a button embeded on the "Source" worksheet If the source data isn't organized as I have it, please let me know. Use the <PRE> and </PRE> tags (see icon above reply box) as text within these tags generally keeps its formatting/position Regards

Humar, this looks absolutely fantastic, I'll dig in and have a go with it. many thanks

given record data of the form (not comma delimited of course): League 2008/09, 31/10/2009,team_a,team_b,1-0 League 2008/09, 05/11/2009,team_a,team_b,3-2 ... output is: League 2008/09,31/10/2009 League 2008/09,05/11/2009 -its picking up title and date only, missing out fields with Team_A, Team_B, and score: 1-0, 2-3, etc. I'm not sure why but I'm working on it, many thanks for what you've provided its most useful

Hi, When I wrote the code, I thought that each record started with a date on a line on its own, which I see it doesn't, hence the error. I will post a modification based on the format in your last post. Regards