| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create New Workbook from Worksheets in One Workbook | Excel Discussion (Misc queries) | |||
| Many worksheets in a workbook | Excel Discussion (Misc queries) | |||
| Add 6 worksheets in workbook | New Users to Excel | |||
| How do I build a workbook from the worksheets another workbook? | Excel Discussion (Misc queries) | |||
| assemble link from different cells and return value | Excel Worksheet Functions | |||
Linear Mode
