How to merge several spreadsheets in excel having same column headings into one spreadsheet

I have tried to merge data from several sheets having the same colum headings into one spreadsheet in excel 2007 using the Consolidate button in the "Data" tab but that did not work.
I think that this is partly due to that the data in the rows are not of numeric type, they are of string type.
lets say one sheet has the following
A             B          C        D
Michael   Car       CEO   MARRIED
Sara        Van     Secrt  Single

Another sheet has the following data from another company

A             B          C        D
Josh        Car      CEO   MARRIED
Ray         Van     Tech  MARRIED

So in Sheet 3 I would expect to see

A             B          C        D
Michael   Car       CEO   MARRIED
Sara        Van     Secrt  Single
Josh        Car      CEO   MARRIED
Ray         Van     Tech  MARRIED

I have selected several options in the Consilidate function but I was not able to get it to work
I have attached the reference file for testing
test.xlsx
LUI1984Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nutschCommented:
I don't think you have the right example file, but here is a code that should do it for you on all sheets of a workbook.

Thomas

Sub consolidateSheets()
Dim shtDone As Worksheet, lstRow As Long
Dim wksht As Worksheet, firstSheet As Boolean

Const bolTitles As Boolean = True 'True if sheets have titles, false if they don't
Const strSummary As String = "All" ' update to the name of the consolidated destination
Const bolTab As Boolean = True 'get data from tab name ? True / False
Const strTabTitle As String = "Year" 'title of column from tab name if bolTab=true
Dim lgTabCol As Long

application.ScreenUpdating = False
application.DisplayAlerts = False

Set shtDone = ActiveWorkbook.Sheets.Add

On Error Resume Next
shtDone.Name = strSummary

If Err.Number <> 0 Then
    ActiveWorkbook.Sheets(strSummary).Delete
    shtDone.Name = strSummary
    Err.Clear
End If

firstSheet = True

For Each wksht In ActiveWorkbook.Sheets
    
    If wksht.Name = strSummary Then GoTo nxtSht
    
    wksht.[A1].CurrentRegion.Copy
    
    lstRow = shtDone.range("A" & Rows.Count).End(xlUp).Row
    
    shtDone.Select
    shtDone.range("A" & lstRow + 1).Select
    ActiveSheet.Paste

    If bolTitles = True And firstSheet = False Then
        Rows(lstRow + 1).Delete
    Else
        If bolTab = True And firstSheet = True Then
            lgTabCol = shtDone.Cells(2, Columns.Count).End(xlToLeft).Column + 1
            shtDone.Cells(2, lgTabCol) = strTabTitle
            lstRow = lstRow + 1
        End If
    End If
    
    If bolTab = True Then
        shtDone.Cells(lstRow + 1, lgTabCol) = wksht.Name
    End If
        
    firstSheet = False

nxtSht:
Next

If bolTab = True Then
    Intersect(ActiveSheet.UsedRange, Columns(lgTabCol)).Offset(1, 0).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    Columns(lgTabCol).Copy
    ActiveSheet.PasteSpecial Paste:=xlPasteValues
    application.CutCopyMode = False
End If

application.DisplayAlerts = True
application.ScreenUpdating = True
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LUI1984Author Commented:
Thank you for your prompt reply nutsch, but I am trying not to use code because I am trying to publish this through the Excel Web Access Webpart and that might not work with code. Is it possible to do throught normal excel functions?
Stacy BrownSenior Applications AdministratorCommented:
You cannot use the consolidate tool for text.  In order to combine these sheets you are going to need to use a macro or manually copy and paste the information.  Excel just doesn't have an easy way to do this for text.

Here's some great information on using macros for this job: http://www.rondebruin.nl/copy2.htm
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.