Consolidating data

[pcarroll]

I have a spreadsheet put together to compile information. I put all of line item information into a seperate sheet. Can Excel put that info in a recap sheet for me? How do I move multiple lines of text from multiple worksheets to a recap sheet where I can consolidate the information.
Thanks for the help

 

[Jim North]

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

 

[pcarroll]

Thanks for the help. That looks like some pretty fancy info so I will plug away at it this week and post my success or failure.
Thanks again

 

[pcarroll]

I am actually looking to transfer mostly text from indivudual sheets to a main recap sheet. I have about 100+ individual worksheets and if I enter information in 50 of them, I would like that information sent to the recap sheet.

Thanks

 

[Jim North]

Hello pcarrol,

I'm not trying to scare you or make your life difficult. Based on what information you've provided so far, I supply you with the above VBA subprocedure. Tell us a little more... how many columns are there, how often you want them merged, what is the source of your data (is it electronic?). Thanks for the imformation!