Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merging Workbooks (97 SR2)

    I been searching Excel Help to try and figure out how to merge workbooks. I've found a few references to 'troubleshoot merging workbooks' and the like, but no explanation on how to actually get to the point where troubleshooting is necessary.

    I will have 20 outside contacts each sending me an e-mail with an excel attachment. (I created the attachment and have locked it, except for five columns where they will be entering data). I have asked that they not change the sheet in any way, other than to enter the data). What I want to end up with is one workbook with 21 worksheets, 1 from each contact plus 1 summary/total sheet.

    I know I can do a 'simple' copy and paste of the five columns but I know, from previous experience, that I would probably mess it up. Adding worksheets to my Workbook seems to involve less room for human error.

    What am I missing? <img src=/S/help.gif border=0 alt=help width=23 height=15> Please accept my abject apologies in advance if it is staring me in the face <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

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

    Re: Merging Workbooks (97 SR2)

    If you open both your main workbook and one of the attachments, you can move or copy sheets between them.

    One way is to place the windows side by side (using the Window/Arrange... menu option) and to drag a sheet tab from one window to another. If you keep the Ctrl key down while releasing the mouse button, the sheet is copied rather than moved.

    Another way is to right-click a sheet tab, select Move or Copy Sheet... from the context menu and then select the workbook the sheet should be moved/copied to. (This is the same as the Edit/Move or Copy Sheet... menu option)

    If you have to do this only occasionally, it can be done manually. If you have to do it frequently, however, a macro might be more efficient.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging Workbooks (97 SR2)

    Wow. Thanks Andrew!

    Did you have that bit of code handy, or did you just let it roll from your fingertips? <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

    Many thanks

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Merging Workbooks (97 SR2)

    The following code will merge all the workbooks in a given Folder into the active workbook.<pre>Sub CombineSheets()
    Dim oFSO, oFolder, oFile
    Set oFSO = CreateObject("Scripting.FilesystemObject")
    Set oFolder = oFSO.GetFolder(FolderName)
    For Each oFile In oFolder.Files
    ActiveWorkbook.Sheets.Add Type:= oFile.Path
    Next
    End Sub</pre>

    replacing FolderName with the path to the folder.

    You could place all the files you wish to merge into a folder and run the above.

    Andrew C

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging Workbooks (97 SR2)

    Hans
    Thanks very much. Obvious - when you know how. I should have experimented a little more. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>. I'll use your method for now, before attempting Andrew's code. I always wanted an excuse for getting into VBA for Excel. (Now where did I put my "Hacker's Guide to Excel" <img src=/S/question.gif border=0 alt=question width=15 height=15>)

Posting Permissions

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