MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Merging a variety of workbooks/sheets together

Posted by Anita on February 01, 2002 1:15 PM

Is there any way that I could merge certain date from a variety of sheets within a variety of workbooks into one individual workbook? I am trying to merge together 32 sheets in 26 different workbooks. Basically, what I want to do is consolidate all the data. Thanks to anyone who can help!


Posted by Ed Acosta on February 01, 2002 7:10 PM

It is possible here is code that will do it. I made some assumptions but it can easily be modified to do what you need done. Basically create a folder, in this case I named it Excel Test on my d drive, and copy all the 26 workbooks into the folder. If you can e-mail specifics I can adjust it for your needs.

Function ConsolidateData()
Dim T, X, SH, RW As Long
Dim FN(26)
' Following section reads in all Excel file
names located in Excel Test folder
FN(1) = Dir("D:\Excel Test\*.xls")
For T = 2 To 26
FN(T) = Dir
Next T

' Following section cycles through all books and all sheets in those books
For T = 1 To 26
SH = 0: RW = 0 'Reset Row (RW) and Sheet (SH) counter
Application.ScreenUpdating = False 'Turn off Screen Updating to Run Faster
Application.DisplayAlerts = False 'Turn off alerts Remove this line to debug
Workbooks.Open ("D:\Excel Test\" & FN(T)) 'Open Workboook to work on
For SH = 1 To Sheets.Count 'Count how many sheets the Workbook has
Sheets(SH).Activate 'Activate the first sheet
RW = Range("A65536").End(xlUp).Row 'Determine how many rows the sheet has
For X = 2 To RW 'Cycle through each row in the spreadsheet
CV = Range("A" & X).Value 'Get the value in Column A for Row X
If CV = "2/1/2002" Then 'Determine if the value equals the date we need
Rows(X).Select 'If yes Select the entire row
Selection.Copy 'Copy the Row
Workbooks("compile.xls").Activate 'Activate the workbook data is to be compiled to
Sheets("sheet1").Select 'Select the sheet data is to be copied to
NextRow = Range("A65536").End(xlUp).Row + 1 'Determine the next available row
Range("A" & NextRow).Select 'Select the next available row
ActiveSheet.Paste 'Paste the Data
Workbooks(FN(T)).Activate 'Re activate working workbook
Sheets(SH).Select 'Re activate working sheet
End If
Next X 'Cycle to the next Row
Next SH 'Cycle to the next Sheet
Workbooks(FN(T)).Close 'Close the workbook once all sheets have been reviewed
Application.ScreenUpdating = True 'Re activate Screen Updating
Application.DisplayAlerts = True 'Re activate alerts
Next T 'Cycle to the next workbook

End Function

Posted by Anita on February 04, 2002 8:46 AM

Ed, I think I can work with this, thanks so much for your help!