Combine 30 worksheets into 1 workbook (ExcelXP)

[Gwynne]

Help! I need some VBA code to give my managers the ability to combine some of the 30 separate worksheets into one workbook. Can this be done? If so...how? Very new to VBA so be gentle.

[HansV]

You will need to give some more information before we can give specific help.

Is each worksheet a separate Excel file?
If so, are they all in the same folder?
If so, are they ALL files in that folder, or are there other files in that folder?
If not, how do you determine which worksheets to combine?

[Gwynne]

Yes, Hans, each worksheet is in a separate workbook. This is for grading our employees for pay raises. Each employee has a workbook/file/folder with his/her name. Each of those workbooks have 1 worksheet with the employees score/information. There are 3 managers who will need to select their subordinates and combine their worksheets into one workbook for each manager. This sounds like a monumental job, but the managers are not very computer literate, so most computer related tasks have to be extremely simple. I would be very grateful for any help that you can give me on getting started with this project.

[HansV]

If I understand you correctly, there is a separate folder (directory) bearing the name of the employee, and each of these folders contains an Excel workbook, also named after the employee, with one worksheet.
Are all these folders subfolders of the same folder, or are they in different locations?

[Gwynne]

I

[HansV]

And I'm sorry to keep on asking questions, but you still haven't made everything clear.

Every manager has a number of Excel files in a folder; these are to be combined into one Excel file. But, as I asked earlier, are there other (Excel) files in that folder, that should NOT be included, or can ALL Excel files in that specific folder be combined into a new file?

[Gwynne]

Sorry I didn't answer your questions. There will not be other Excel files in the folder...ALL files in each folder will be combined into the new folder.

[HansV]

Try this:
- Activate the Visual Basic Editor (Alt+F11)
- Select Insert | Module
- Copy the code below into the module.
- Switch back to Excel (Alt+F11)
- Save your workbook.
- Select Tools | Macro | Macros...
- Select Combine in the list of available macros
- Click Run.

<img src=/w3timages/blueline.gif width=33% height=2>

Public Function BrowseFolder(Optional Title As String = "Select a Folder", _
Optional RootFolder As Variant) As String
On Error Resume Next
BrowseFolder = CreateObject("Shell.Application").BrowseForFolder( 0, Title, 0, RootFolder).Items.Item.Path
End Function

Sub Combine()
Dim strPath As String
Dim strFile As String
Dim wbkSource As Workbook
Dim wbkTarget As Workbook

On Error GoTo ErrHandler

strPath = BrowseFolder
If strPath = "" Then
Exit Sub
End If

Set wbkTarget = Workbooks.Add

strFile = Dir(strPath & "*.xls")
Do While Not (strFile = "")
Set wbkSource = Workbooks.Open(Filename:=strPath & "" & strFile, AddToMRU:=False)
wbkSource.Worksheets.Copy After:=wbkTarget.Worksheets(wbkTarget.Worksheets.C ount)
wbkSource.Close SaveChanges:=False
strFile = Dir
Loop

ExitHandler:
Set wbkSource = Nothing
Set wbkTarget = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

<img src=/w3timages/blueline.gif width=33% height=2>

[Gwynne]

This works wonderfully!!! <img src=/S/mice.gif border=0 alt=mice width=50 height=25> Thank you and have a very Merry Christmas and Happy New Year.

[BakerMan]

How could this be modified to select particular workbooks from the selected folder?

[LegareColeman]

The code in <!post=This Post,426136>This Post<!/post> can be used to let the user select multiple files from the file selection dialog box.