Merge Excel Sheets Using VBA

I have a Excel Sheet(Say OG.xls) which has some data already in it with some 5000 rows with headings in the first row and Upto "AN" Columns. This No of rows(5000) doesn't change for a whole year. Now i have 5 XL files(Say A,B,C,D,E) and the data from these files has to be appended to this OG file just starting from 5001st row every time. All these 5 files has different no of columns but identical to that of OG File. I have to pull data from these files and place them in OG File. From File A : Column A,B,C,D,E,F,G&H goes to Column F,G,T,U,V,W,X&Y Of OG.xls File. Likewise the other files data has to be extracted according to the corresponding column with OG.xls

The second file data has to be appended right below the next row where the File A ends.(Say after filling the data from File A now the OG.xls has 5110 rows, the File B data has to filled from 5111 st row of OG.xls. The same follows for the other files too. The data of these 5 files has to be filled row after row but should match the columns to that of OG.xls

Each time the same operation is repeated by filling the data from 5001st row of OG.xls. For convenience we can have all these files in a same folder.

How can we do this.

Please help me in this!!! Also let me know for any clarifications.

From stackoverflow $pageUsers[$entry.posts[0].author].name

• If you need a more presice answer, you would need to try something first and then ask for help in area you have got stuck. My suggestion is you begin by; 1. Start writing a VBA script in OG.XLS, as a first step try to access the file A.xls and reading the columns and pasting them (they can initially be at any location in any order). 2. Once you are able to do this, next step is to see if you put the data in right column (say 5000 in your example) by setting up right kind of variables and using them and incrementing them. 3. Your next step should be to to read the column headings in A.XLS and finding them OG.XLS and identifying them. Initially you can begin by doing a simple string comparision, later you can refine this to do a VLOOKUP. 4. During this process, if you encounter any specific problem, raise it so that you will get a better answer.

  Few from the community would go to the extent of writing the entire code for you.

  From Dheer

• Why does Column A end up in Column F, and why does C end up in T? Is there a rule around this such as the first row is a header with with the same text in it?

  Maybe a picture might help.

  Based on what i can guess, i'd throw each sheet into a RecordSet with meaningful field names (you'll need to reference Microsoft ActiveX Data Objects 2.8 Library) . Once done it will be very easy to append each RecordSet and throw them into a single sheet.

  You'll need to be able to find the last column and last row in each sheet to do this cleanly so have a look at How can i find the last row...

  Edit...

  Below is a cleaned up example of how you could do what you need in VBA. The devil is in the details such as empty sheets, and how to handle formulas (this ignores them completely), and how to merge you columns in an appropriate way (again ignored).

  This has been tested in Excel 2007.

  Option Explicit
  Const MAX_CHARS = 1200
  
  
  
  Sub MergeAllSheets()
    Dim rs As Recordset
    Dim mergedRS As Recordset
    Dim sh As Worksheet
    Dim wb As Workbook
  
    Dim fieldList As New Collection
    Dim rsetList As New Collection
  
    Dim f As Variant
    Dim cols As Long
    Dim rows As Long
    Dim c As Long
    Dim r As Long
  
    Dim ref As String
    Dim fldName As String
    Dim sourceColumn As String
  
  
  
    Set wb = ActiveWorkbook
    For Each sh In wb.Worksheets
      Set rs = New Recordset
      ref = FindEndCell(sh)
      cols = sh.Range(ref).Column
      rows = sh.Range(ref).Row
  
      If ref <> "$A$1" Or sh.Range(ref).Value <> "" Then '' This is to catch empty sheet
        c = 1
        r = 1
        Do While c <= cols
          fldName = sh.Cells(r, c).Value
          rs.Fields.Append fldName, adVarChar, MAX_CHARS
          If Not InCollection(fieldList, fldName) Then
            fieldList.Add fldName, fldName
          End If
          c = c + 1
        Loop
        rs.Open
  
  
        r = 2
        Do While r <= rows
          rs.AddNew
          c = 1
          Do While c <= cols
            rs.Fields(c - 1) = CStr(sh.Cells(r, c).Value)
            c = c + 1
          Loop
          r = r + 1
          Debug.Print sh.Name & ": " & r & " of " & rows & ", " & c & " of " & cols
        Loop
        rsetList.Add rs, sh.Name
      End If
    Next
  
  
    Set mergedRS = New Recordset
    c = 1
    sourceColumn = "SourceSheet"
    Do While InCollection(fieldList, sourceColumn) '' Just in case you merge a merged sheet
      sourceColumn = "SourceSheet" & c
      c = c + 1
    Loop
    mergedRS.Fields.Append sourceColumn, adVarChar, MAX_CHARS
    For Each f In fieldList
      mergedRS.Fields.Append CStr(f), adVarChar, MAX_CHARS
    Next
    mergedRS.Open
  
    c = 1
    For Each rs In rsetList
      If rs.RecordCount >= 1 Then
        rs.MoveFirst
        Do Until rs.EOF
          mergedRS.AddNew
          mergedRS.Fields(sourceColumn) = "Sheet No. " & c
          For Each f In rs.Fields
            mergedRS.Fields(f.Name) = f.Value
          Next
          rs.MoveNext
        Loop
      End If
      c = c + 1
    Next
  
  
    Set sh = wb.Worksheets.Add
  
    mergedRS.MoveFirst
    r = 1
    c = 1
    For Each f In mergedRS.Fields
      sh.Cells(r, c).Formula = f.Name
      c = c + 1
    Next
  
    r = 2
    Do Until mergedRS.EOF
      c = 1
      For Each f In mergedRS.Fields
        sh.Cells(r, c).Value = f.Value
        c = c + 1
      Next
      r = r + 1
      mergedRS.MoveNext
    Loop
  End Sub
  
  Public Function InCollection(col As Collection, key As String) As Boolean
    Dim var As Variant
    Dim errNumber As Long
  
    InCollection = False
    Set var = Nothing
  
    Err.Clear
    On Error Resume Next
      var = col.Item(key)
      errNumber = CLng(Err.Number)
    On Error GoTo 0
  
    '5 is not in, 0 and 438 represent incollection
    If errNumber = 5 Then ' it is 5 if not in collection
      InCollection = False
    Else
      InCollection = True
    End If
  
  En
                                                      
  
                                                                      
  
                                          Mark Nold : Perfect, reading each sheet into a recordset with the first row as your field names should be a breeze. Merging them shouldn't be too hard either. Getting the columns in the right order may be tricky unless you specify it in your first sheet.
                                      
                                                              
                                                  
  
                              From Mark Nold
                          
                      

• I come across this problem when one of my client came to me looking for solution to merge their stock lists that are saved in more than 200 separate files. If you found yourself to be in the same position like my client; don’t worry, I wrote a simple program that do the work. :) Check the link below:

  JMC Excel - Join, Merge, Combine multiple Excel sheets or Excel workbooks

  Regards, JeeShen Lee www.jeeshenlee.wordpress.com

  From JeeShen Lee