Consolidating records from multiple Workbook

[Crazy1923]

Hi,

Can you please help me how to consolidate records for example i have 12 files and i need to consolidate records to a file. also when i update the source file, the consolidated file will also add the record to the last.

Thanks

 

[Crazy1923]

Any solution on this???

 

[John Davis]

Any solution on this???

Does this help?

'Combine Workbooks
'By Tommy Miles
'This sample goes through all the Excel files in a specified directory and combines theminto
'a single workbook.  It renames the sheets based on the name of the original workbook:
Sub CombineWorkbooks()
Dim CurFile As String, DirLoc As String
Dim DestWb As Workbook
Dim ws As Object 'allows for different sheet types

DirLoc = ThisWorkbook.Path & "\tst\" 'location of files
CurFile = Dir(DirLoc & "*.xls")

Application.ScreenUpdating = False
Application.EnableEvents = False

Set DestWb = Workbooks.Add(xlWorksheet)

Do While CurFile <> vbNullString
    Dim OrigWb As Workbook
    Set OrigWb = Workbooks.Open(Filename:=DirLoc & CurFile, ReadOnly:=True)
    
    ' Limit to valid sheet names and remove .xls*
    CurFile = Left(Left(CurFile, Len(CurFile) - 5), 29)
    
    For Each ws In OrigWb.Sheets
        ws.Copy After:=DestWb.Sheets(DestWb.Sheets.Count)
        
        If OrigWb.Sheets.Count > 1 Then
            DestWb.Sheets(DestWb.Sheets.Count).Name = CurFile & ws.Index
        Else
            DestWb.Sheets(DestWb.Sheets.Count).Name = CurFile
        End If
    Next
    
    OrigWb.Close SaveChanges:=False
    CurFile = Dir
Loop

Application.DisplayAlerts = False
    DestWb.Sheets(1).Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True
Application.EnableEvents = True

Set DestWb = Nothing

End Sub