The simple answer is to cut and paste. I am assuming since you are asking you may be looking for a more "automated" way. The follow VBA sub procedure will copy records from worksheet to another... it can be a starting point for you. Some of the things you will need to be concerned about is the next available row in the summary worksheet and getting the last row number for every worksheet you access.
Hope this helps. When I return to work on Monday I will check this post and see how you are doing.<pre>
Sub MergeRows()
Const xlColumnWidths = 8 'to work around known Excel VBA bug
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim Sell As Range
Dim CurrRow As Long
Dim KeyFld As String
Dim LastRow As Long
Dim NewRow As Long
Dim LastKeyFld As String
' turn off screen updating
Application.ScreenUpdating = False
' point to the worksheets
Set wks1 = Worksheets("Sheet1")
Set wks2 = Worksheets("sheet2")
' clear Sheet2
wks2.Columns("A:IV").Clear
' copy the headings to Sheet2
wks1.Rows("1:1").Copy
wks2.Rows("1:1").PasteSpecial Paste:=xlAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' set the columns widths on Sheet2
wks1.Rows("1:1").Copy
wks2.Rows("1:1").PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' point to Sheet1
wks1.Activate
' get the number of rows used on Sheet1
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
NewRow = 1
' set the range to process to column A
Set OldRange = Range("A2:A" & LastRow)
For Each Sell In OldRange
CurrRow = Sell.Row
If Cells(CurrRow, 8).Value > 0 Or Cells(CurrRow, 13).Value > 0 Then
' build comparison key
KeyFld = Trim(Cells(CurrRow, 1).Value) & Trim(Cells(CurrRow, 4).Value) & _
Trim(Cells(CurrRow, 5).Value) & Trim(Cells(CurrRow, 12).Value)
If KeyFld = LastKeyFld Then
'store the charge amount
If Cells(CurrRow, 8).Value > 0 Then
wks2.Range("H" & NewRow).Value = wks1.Range("H" & CurrRow).Value
End If
'store the haul rate
If Cells(CurrRow, 13).Value > 0 Then
wks2.Range("M" & NewRow).Value = wks1.Range("M" & CurrRow).Value
End If
Else
LastKeyFld = KeyFld 'store the new key
NewRow = NewRow + 1 ' increment the new row for sheet2
' copy the row from sheet1 to sheet2
wks1.Range(CurrRow & ":" & CurrRow).Copy
wks2.Range(NewRow & ":" & NewRow).PasteSpecial Paste:=xlAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
End If
Next Sell
'point to cell A1 on Sheet2
wks2.Activate
Range("A1").Select
Application.ScreenUpdating = True
End Sub<pre>
_________________
JRN
Excel 2000; Windows 2000
This message was edited by Jim North on 2002-08-24 17:29