Consolidating multiple files into one excel file with multiple tabs

**Chemistification** · 06-07-2012, 06:05 AM

Hi all,

I would really appreciate it if someone could give me advice on if the following is possible.

I will be saving files into one folder in the following manner:

Project ABC June report (Sheets = mikes summary, mikes budget, mikes risks)
Project DEF June report (Sheets = john summary, john budget, john risks)
Project XYZ June report (Sheets = jane summary, jane budget, jane risks)

The number of files may vary month by month depending on projects on the go.

I would like to create a master file that has a macro in it that pulls the first sheet (and only the first sheet) from each of the other workbooks and keeps them in individual sheets in the master document.

So the master document would look like

Master document (Sheets = Mikes summary, Johns summary, janes summary etc)

Is this do-able?

Thanks in advance for looking at it.

**arlu1201** · 06-07-2012, 06:55 AM

Whats the range that should be copied from each sheet?

Will the master file be a blank file to start with?

When you re-run the macro (maybe the following week or so), do you want the contents of the master file to be cleared and fresh data included?

**Chemistification** · 06-07-2012, 08:26 AM

Hi Arlette,

Thanks for replying. The range would be the entire of the first sheet (actual data is A1:H56 but their is a potential this could change)

So long as the master file ended up with a copy of each of the different projects summary sheets then i dont mind if it is blank to begin with or has data in

When i re run the macro i would want the contents to be cleared and fresh data put in

Dhanyavadagalu

**Chemistification** · 06-13-2012, 05:03 AM

Hello all. I posted this on here a couple of days ago but it didn't seem to get much interest. Anyone able to help me out?

Many thanks,

Chemist

**arlu1201** · 06-13-2012, 06:23 AM

Try this code

Option Explicit

Sub cons_data()

Dim Master As Workbook
Dim sourceBook As Workbook
Dim sourceData As Worksheet
Dim CurrentFileName As String
Dim myPath As String
Dim i As Long
Dim sname As String

'The folder containing the files to be recap'd
myPath = "D:\Test"

'Finds the name of the first file of type .xls in the current directory
CurrentFileName = Dir(myPath & "\*.xlsx")

'Create a workbook for the recap report
Set Master = ThisWorkbook

For i = 1 To Master.Worksheets.Count
    Master.Worksheets(i).Cells.ClearContents
Next i

Do
    Workbooks.Open (myPath & "\" & CurrentFileName)
    Set sourceBook = Workbooks(CurrentFileName)
            
        With sourceBook
            For i = 1 To .Worksheets.Count
                If .Worksheets(i).Name Like "* summary" Then
                    sname = .Worksheets(i).Name
                    Master.Activate
                    If Not Evaluate("ISREF('" & sname & "'!A1)") Then
                        Master.Worksheets.Add(After:=Master.Worksheets(Worksheets.Count)).Name = sname
                    End If
                    .Worksheets(sname).Cells.Copy Master.Worksheets(sname).Range("A1")
                    Exit For
                End If
            Next i
        End With
       
    sourceBook.Close
  
'Calling DIR w/o argument finds the next .xlsx file within the current directory.
CurrentFileName = Dir()
Loop While CurrentFileName <> ""

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button