copy cells from sheet a to sheet b

Microsoft Microsoft excel 2000 95/98/me/...
November 5, 2009 at 03:16:52
Specs: Windows 2000 sp4
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


See More: copy cells from sheet a to sheet b

Report •


#1
November 5, 2009 at 05:48:19
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


Report •

#2
November 5, 2009 at 06:29:48
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


Report •

#3
November 5, 2009 at 07:53:06
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


Report •

Related Solutions

#4
November 5, 2009 at 08:11:24
Humar, this looks absolutely fantastic, I'll dig in and have a go
with it. many thanks

Report •

#5
November 5, 2009 at 09:42:43
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


Report •

#6
November 5, 2009 at 09:57:08
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


Report •

#7
November 5, 2009 at 10:12:35
Hi,

Try this replacement for the main code loop:

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 League
            If Left(Trim(rngCell.Value), 6) = "League" Then
                'move to next destination row
                intDest = intDest + 1
                'move date to column 1
                rngCell.Copy Destination:=.Worksheets("Final").Cells(intDest, 1)
                Else
                'does not start with League, so move four cells to columns 2 to 5
                .Worksheets("Source").Range(Cells(rngCell.Row, 1), Cells(rngCell.Row, 4)).Copy _
                    Destination:=.Worksheets("Final").Cells(intDest, 2)
            End If
            'reset end counter
            intEndCount = 10
        End If
    Next rngCell
End With

It tests for "League" at the start and if it finds it, it starts a new row. A line starting without the word League is treated as the rest of the data to go into the columns following the League name.

The problem with code like this is that it is very data specific, so small changes in data format will mess it up !

Anyway, give it a try.

Regards


Report •

#8
November 5, 2009 at 10:48:57
hey thanks again Humar, I'll take a look

Report •


Ask Question