assemble worksheets into one workbook
 

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!

assemble worksheets into one workbook
 

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



"Jill" wrote in message
...
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!

assemble worksheets into one workbook
 

That works great, thanks!

"Bernie Deitrick" wrote:

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



"Jill" wrote in message
...
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!

assemble worksheets into one workbook
 

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

"Jill" wrote:

That works great, thanks!

"Bernie Deitrick" wrote:

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



"Jill" wrote in message
...
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!

assemble worksheets into one workbook
 

For office 2007 it is not working ?

"Neil" wrote:

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

"Jill" wrote:

That works great, thanks!

"Bernie Deitrick" wrote:

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



"Jill" wrote in message
...
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!