assemble worksheets into one workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
That works great, thanks!

Bernie Deitrick said:
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
 
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
 
For office 2007 it is not working ?

Neil said:
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
 
Back
Top