Collapsing the data


Posted by Amit on January 02, 2002 12:11 PM

I have data in Excel which is spread in about 50 different sheets,I need to collapse it all into a single sheet,It would be great if I could get a simpler way other than the painful ordeal of cutting and pasting each sheet
Thanks,
Amit

Posted by Damon Ostrander on January 02, 2002 2:05 PM

Hi Amit,

It is easy in VBA to write a macro that collapses data from many worksheets to one. The specifics of the code depend on how you want the data collapsed. For example, does each worksheet contain a number of rows of data, and you want the rows from all the sheets appended onto the rows of the one sheet? Or are there columns of data that must be appended? Or perhaps even blocks (ranges) of data on each sheet that must be arranged in some way on the one sheet? Are there header rows or columns on the individual sheets that you don't want moved?

Most likely, the problem can be solved with 10-20 lines of code, but again, can't be answered without specifics.

Damon

Posted by AMIT on January 03, 2002 10:21 AM

I just want all the data to be appended into one sheet,There are 16 variables in he header row of each sheet,I want that to remain same and just the data to be added all into a single sheet HELP




Posted by Damon Ostrander on January 03, 2002 12:49 PM

Hi again Amit,

Okay, let me know if this code does what you want.

Sub MergeSheets()

' This macro appends the rows of data on all sheets (except 1) to
' worksheet 1 (worksheet 1 is the leftmost tab), and processes the
' sheets in left-right tab order. The first row on each sheet is
' assumed to be a header row and is ignored.

Dim iWS As Integer
Dim WS As Worksheet
Dim LastRow1 As Long
Dim LastRowN As Long

For iWS = 2 To Worksheets.Count
Set WS = Worksheets(iWS)
With Worksheets(1)
LastRow1 = .UsedRange.Rows.Count
LastRowN = WS.UsedRange.Rows.Count
WS.Range(WS.Rows(2), WS.Rows(LastRowN)).Copy .Rows(LastRow1 + 1)
End With
Next iWS

'uncomment the following lines if you want to delete all the
'collapsed sheets when done.

'Application.DisplayAlerts = False
'For Each WS In Worksheets
' If WS.Index <> 1 Then WS.Delete
'Next WS
'Application.DisplayAlerts = True

End Sub

Happy computing.

Damon