Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Worth, Texas, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine 30 worksheets into 1 workbook (ExcelXP)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine 30 worksheets into 1 workbook (ExcelXP)

    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?

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Worth, Texas, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine 30 worksheets into 1 workbook (ExcelXP)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine 30 worksheets into 1 workbook (ExcelXP)

    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?

  5. #5
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Worth, Texas, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine 30 worksheets into 1 workbook (ExcelXP)

    I

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine 30 worksheets into 1 workbook (ExcelXP)

    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?

  7. #7
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Worth, Texas, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine 30 worksheets into 1 workbook (ExcelXP)

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine 30 worksheets into 1 workbook (ExcelXP)

    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>

  9. #9
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Worth, Texas, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine 30 worksheets into 1 workbook (ExcelXP)

    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.

  10. #10
    BakerMan
    Guest

    Re: Combine 30 worksheets into 1 workbook (ExcelXP)

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

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine 30 worksheets into 1 workbook (ExcelXP)

    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.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •