How do I combine multiple whooksheets with identical columns
 

Hi

I have multiple records to be updated by different users, I would like to
combine them all into one worksheet without going through the pain to cut &
paste each one of them. Is there a quick way to combine them?

Pls help! Thanks.

HF,

Use a macro: see below, expecially the comments.

HTH,
Bernie
MS Excel MVP


Sub Consolidate()
' Will consolidate Mulitple Sheets
' from Multiple Files onto one sheet
' Never tested with files that would
' give more than one sheets as end result
' Assumes that all data starts in cell A1 and
' is contiguous, with no blanks in column A

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel"
.SearchSubFolders = False 'Change to true if needed
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set Basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
For Each mySheet In myBook.Worksheets
mySheet.Activate
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(1).Range("a65536").End(xlUp).O ffset(1,
0)
Next mySheet
myBook.Close
Next i
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Basebook.SaveAs Application.GetSaveAsFilename

End Sub

"HF" wrote in message
...
Hi

I have multiple records to be updated by different users, I would like to
combine them all into one worksheet without going through the pain to cut
&
paste each one of them. Is there a quick way to combine them?

Pls help! Thanks.