assemble worksheets into one workbook

[Guest]

Is there an easy macro I can use to take all the workbooks in a folder and
save them as a single workbook with multiple tabs?

Each workbook has only one tab, and I would like to save them in one
workbook with multiple tabs.

I know this can be done manually, but I have to do it every day, so a macro
would be much easier.

Thanks!

 

[Bernie Deitrick]

Jill,

Copy this macro into an otherwise blank workbook.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
Dim i As Integer
With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "H:\USERS\Jill"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
ActiveSheet.Name = Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4)
ActiveSheet.Move After:=ThisWorkbook.Sheets(1)
Next i
Else: MsgBox "There were no files found."
End If

End With

End Sub

 

[Guest]

That works great, thanks!

Jill,

Copy this macro into an otherwise blank workbook.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
Dim i As Integer
With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "H:\USERS\Jill"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
ActiveSheet.Name = Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4)
ActiveSheet.Move After:=ThisWorkbook.Sheets(1)
Next i
Else: MsgBox "There were no files found."
End If

End With

End Sub

 

[Neil]

Bernie or anyone who can help,

I am trying to do the same thing. You say copy it into a blank workbook?
What exactly do you mean by this? Do i have to create a new macro?

Thanks

 

[Shiv]

For office 2007 it is not working ?

Bernie or anyone who can help,

I am trying to do the same thing. You say copy it into a blank workbook?
What exactly do you mean by this? Do i have to create a new macro?

Thanks