consolidating workbooks

[Emma]

I have data in a dozen separate workbooks, each of which
has a worksheet for each month of the year. The workbooks
bear the name of the employee who enters the applicable
date. I want to be able to consolidate the data into one
workbook. Have tried consolidate with strange results.
Am currently attempting pivot table, but have never done
this before. Seems to be working, but gives me
twelve "items" instead of workbook/employee names. I need
to be able to determine in the consolidated data, which
employee was responsible for which piece of data. Can
provide further details if helpful. Would appreciate any
suggestions.

 

[Bernie Deitrick]

Emma,

Try the sub below.

Assumes: you want the workbook name in column A, the sheet name in column B,
and the data from the currentregion around A1 copied into the summary
starting at column C.

Put all your workbooks into a single folder, then change the folder name in
the macro where indicated.

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("C65536").End(xlUp).Offset(1,
0)
With Basebook.Worksheets(1)
.Range(.Range("A65536").End(xlUp).Offset(1, 0), _
.Range("C65536").End(xlUp).Offset(0, -2)).Value = _
myBook.Name
.Range(.Range("B65536").End(xlUp).Offset(1, 0), _
.Range("C65536").End(xlUp).Offset(0, -1)).Value = _
mySheet.Name
End With
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

 

[Emma]

Bernie,

Thanks much for this. Too advanced for my understanding,
I fear. I copied the macro below into Excel, changed the
folder name to the one where the workbooks are located,
then tried running the macro. I get an Excel error
message, just 400 (?!), and when I click on OK the error
message goes away and the first (alphabetically) workbook
is open but the worksheet in the blank workbook where I
ran the macro is unchanged.

One of the problems I've encountered in using the Data
Consolidate function is that some cells will be blank,
others will have data. I can keep all data "true" to the
appropriate row by replacing blank cells with a space in
the cell, but then Consolidate counts the spaces as well
as the data. This is a big project and I need to complete
it, but I'm not sure how best to solve these problems.

 

[Bernie Deitrick]

Emma,

If you want, send me a sample workbook, and I will see what about your
workbook structure requires changing of the macro. You can change any
sensitive information to random values prior to sending. Simply modify my
email as needed: take out the spaces and change DOT to .

HTH,
Bernie
MS Excel MVP