Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Copy sheet from many workbooks into one workbook

    Excel 2010.

    I have hundreds of workbooks all in the same folder. They all have the last sheet with the same name (e.g., "final"). There are the same number of columns in each. There are a different number of rows in each. Row 1 in each sheet is identical (column headings).

    I would like to copy the contents of each of these last sheets and PASTE SPECIAL VALUES the contents into a new sheet (let's call it "MERGE") in a new workbook (could also be called "MERGE") so that the final result will be the new workbook with ONE sheet with all of the content from the others appended below each other. (make sense?).

    So, if one workbook's last sheet has 10 rows (not counting the heading row) and 20 columns, those 10 rows go into the new workbook and sheet from row 2 to 11 (because row 1 in the target workbook/worksheet will have the column headings). The next workbook in the folder, the last sheet might have 5 rows and 20 columns. Those would go to the new workbook, same sheet as previous, in rows 12 to 16. Etc., Etc.
    Last edited by kweaver; 2016-04-29 at 12:17.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi

    ..if you do an advanced search in this Lounge using this as your search text:
    Loop through excel files in a directory and copy onto master sheet
    ..it may give you some useful info.

    If not, reply again and we could help

    zeddy

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    I found RDBMerge which seems to do the trick and is really nice.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    KW,

    Here is some code that will allow you to navigate to the folder of your choice. It will cycle through only excel files and retrieve the data from the last sheet of each then append the data to the Merge sheet in the Merge workbook. The code will adjust to the number of rows and columns of the data it needs to transfer. Sheet names are irrelevant. The master workbook Must be called "Merge".

    HTH,
    Maud

    Code:
    Public Sub ListFiles()
    On Error Resume Next
    Application.ScreenUpdating = False
    '--------------------------------------------
    'DECLARE AND SET VARIABLES
    Dim ShellApplication As Object
    Set ShellApplication = CreateObject("Shell.Application").BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
    Path = ShellApplication.self.Path
    Set ShellApplication = Nothing
    Filename = Dir(Path & "\*.*")
    '--------------------------------------------
    'OPEN FILES AND APPEND FROM FINAL SHEET TO MERGE
    NextRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Do While Len(Filename) > 0
        If InStr(1, Right(Filename, 6), ".xl", vbTextCompare) > 1 Then
                Workbooks.Open (Path & "\" & Filename)
                Worksheets(Worksheets.Count).Activate
                LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
                LastCol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column
                Range(Cells(2, 1), Cells(LastRow, LastCol)).Select
                Application.CutCopyMode = False
                Selection.Copy
                Windows("Merge.xlsm").Activate
                Range("A" & NextRow).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
                Application.DisplayAlerts = False
                    Workbooks(Filename).Close SaveChanges:=False
                Application.DisplayAlerts = True
                NextRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
        End If
        Filename = Dir
    Loop
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

Posting Permissions

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