Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine all Workbooks (2003)

    I have a series of workbooks all with the same data. I want to copy them into one workbook, one spreadsheet. Is there a way to do this besides copy and paste? The workbooks are all in the same folder. The workbooks all use columns A:AN. They vary in the number of rows.

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

    Re: Combine all Workbooks (2003)

    See for example <post:=658,535>post 658,535</post:>.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine all Workbooks (2003)

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>

    Thanks Hans, I will use that. Another question: I saw another post (<post#=466943>post 466943</post#>) that said to create a shortcut to a folder that contains all the workbooks. Then place the shortcut in the Templates folder used by Microsoft Office. I tried this and thought I put the shortcut in the templates folder but I must not be in the correct place. I have office 2003. I opened the template folder and put it there (it didn't show up when I right clicked on insert). I tried putting it in each one of the folders that was in the template folder - still no luck. Am I missing something?

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

    Re: Combine all Workbooks (2003)

    The method described in that post would result in a separate worksheet for each workbook, not in a single worksheet containing all the data. So it's probably not what you want.

    The default location for Office templates under Windows XP is Cocuments and Settings<username>Application DataMicrosoftTemplates.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine all Workbooks (2003)

    Thanks Hans you are right that is not what I want but it is nice to know. I have another problem with the macro you suggested. It copies in the headings from each spreadsheet. Is there a way to get just the first heading? If that is too involved, I could just go into the spreadsheets and takes out the heading row except for the first spreadsheet.

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

    Re: Combine all Workbooks (2003)

    You could try this variation of the macro:
    <code>
    Sub MergeFiles()
    ' Path - modify as needed but keep trailing backslash
    Const strPath = "C:Excel"
    Dim strFile As String
    Dim wbkSource As Workbook
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngMaxSourceRow As Long
    Dim lngMaxTargetRow As Long
    Dim blnNoHeader As Boolean

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set wshTarget = ActiveSheet
    strFile = Dir(strPath & "*.xls")
    Do While Not strFile = ""
    Set wbkSource = Workbooks.Open(Filename:=strPath & strFile, AddToMRU:=False)
    Set wshSource = wbkSource.Worksheets(1)
    lngMaxSourceRow = wshSource.Range("A65536").End(xlUp).Row
    lngMaxTargetRow = wshTarget.Range("A65536").End(xlUp).Row
    If blnNoHeader Then
    wshSource.Range("2:" & lngMaxSourceRow).Copy _
    Destination:=wshTarget.Range("A" & (lngMaxTargetRow + 1))
    Else
    wshSource.Range("1:" & lngMaxSourceRow).Copy _
    Destination:=wshTarget.Range("A" & (lngMaxTargetRow + 1))
    End If
    wbkSource.Close SaveChanges:=False
    blnNoHeader = True
    strFile = Dir
    Loop

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub</code>

Posting Permissions

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