Consolidating data


New Member
Aug 22, 2002
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

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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

' copy the headings to Sheet2
wks2.Rows("1:1").PasteSpecial Paste:=xlAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

' set the columns widths on Sheet2
wks2.Rows("1:1").PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' point to Sheet1

' 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

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
Application.ScreenUpdating = True

End Sub<pre>


Excel 2000; Windows 2000
This message was edited by Jim North on 2002-08-24 17:29
Upvote 0
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
Upvote 0
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.

Upvote 0
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!
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back