Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 31

Thread: Collate data from multiple workbooks/sheets into master workbook

  1. #1
    Join Date
    16th August 2010
    Posts
    27

    Collate data from multiple workbooks/sheets into master workbook

    Hi there, I'm a total excel newbie and this is my first post, so please be gentle :-)

    I have multiple workbooks, which contain the same data, which you can see in the attached screenshot.

    I need to create a macro which will read these multiple workbooks from a location and copy the data into a single sheet in the main workbook, which will look just like the sample screen shot(sample sheet.jpg).

    I have found the below macro and it seems to work, however, it has 2 major issues, you can see the results in the second attachment - sample 2.jpg.
    (i) It repeats the headers from each sheet
    (ii) It copies all the blank rows too.

    If anyone can help, i'd be very grateful.

    Thanking you in advance... :-)

    Code:
    Option Explicit
    Sub Get_Value_From_All()
        Dim wbSource As Workbook
        Dim wbThis As Workbook
        Dim rToCopy As Range
        Dim uRng   As Range
        Dim rNextCl As Range
        Dim lCount As Long
        Dim bHeaders As Boolean
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
            On Error Resume Next
            Set wbThis = ThisWorkbook
            'clear the range except  headers
            Set uRng = wbThis.Worksheets(1).UsedRange
            If uRng.Cells.Count <= 1 Then
                'no data in master sheet
                bHeaders = False
                GoTo search
            End If
            uRng.Offset(1, 0).Resize(uRng.Rows.Count - 1, _
                                     uRng.Columns.Count).Clear
    search:
            With .FileSearch
                .NewSearch
                'Change path to suit
                .LookIn = "C:\Excel"
                .FileType = msoFileTypeExcelWorkbooks
                If .Execute > 0 Then    'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count    ' Loop through all.
                        'Open Workbook x and Set a Workbook  variable to it
                        Set wbSource = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                        Set rToCopy = wbSource.Worksheets(1).UsedRange
                        Set rNextCl = wbThis.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)
                        If bHeaders Then
                            'headers exist so don't copy
                            rToCopy.Offset(1, 0).Resize(rToCopy.Rows.Count - 1, _
                                                        rToCopy.Columns.Count).Copy rNextCl
                            'no headers so copy
                            'place headers in Row 2
                        Else: rToCopy.Copy Cells(2, 1)
                            bHeaders = True
                        End If
                        wbSource.Close False     'close source workbook
                    Next lCount
                Else: MsgBox "No workbooks found"
                End If
            End With
     
            On Error GoTo 0
            .ScreenUpdating = True
            .DisplayAlerts = True
            .EnableEvents = True
        End With
    End Sub
    Attached Images
    Last edited by AAE; August 17th, 2010 at 00:21. Reason: revise thread title

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,402

    Re: Help! - Macro to collate data from multiple workbooks/sheets into master workbook

    Please post workbooks, not pictures.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    16th August 2010
    Posts
    27

    Re: Help! - Macro to collate data from multiple workbooks/sheets into master workbook

    oops, sorry about that.

    Here is the sample sheet. All of the sheets will be in this format and the final master will also be like this.

    Many thanks once again :-)
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,402

    Re: Help! - Macro to collate data from multiple workbooks/sheets into master workbook

    In your example, col A goes down to row 20 but you only appear to have one row with data. Is col A fixed and should it be copied to your master file?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    16th August 2010
    Posts
    27

    Re: Help! - Macro to collate data from multiple workbooks/sheets into master workbook

    Hi there, thank you for taking the time to help out.

    Column A is only numbering and is not fixed. I have 20 rows just as a suggestion, but when people complete it, sometimes, only 2-3 rows may contain data, the maximum would be around 50.

    Thanks again :-)

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    16th August 2010
    Posts
    27

    Re: Help! - Macro to collate data from multiple workbooks/sheets into master workbook

    oops, forgot to add, yes please, i would like to retain the numbering in the master file, as the additional data gets appended in the master, column A should grow to reflect the amount of entries hence it could potentially end up at 200+.

    thanks

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,402

    Re: Help! - Macro to collate data from multiple workbooks/sheets into master workbook

    Try this. I have not dealt with col A, as probably easier to add at the end. See if this works for the rest. I haven't tested.
    Code:
    Sub Get_Value_From_All()
        Dim wbSource As Workbook
        Dim wbThis As Workbook
        Dim rToCopy As Range
        Dim uRng   As Range
        Dim rNextCl As Range
        Dim lCount As Long
        Dim bHeaders As Boolean
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
            On Error Resume Next
            Set wbThis = ThisWorkbook
            'clear the range except  headers
            With wbThis.Worksheets(1)
                Set uRng = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
            End With
            If uRng.Rows.Count = 1 Then
                'no data in master sheet
                bHeaders = False
            Else
                uRng.Offset(1, 0).Resize(uRng.Rows.Count - 1, _
                                     uRng.Columns.Count).Clear
            End If
            
    search:
            With .FileSearch
                .NewSearch
                'Change path to suit
                .LookIn = "C:\Excel"
                .FileType = msoFileTypeExcelWorkbooks
                If .Execute > 0 Then    'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count    ' Loop through all.
                        'Open Workbook x and Set a Workbook  variable to it
                        Set wbSource = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                        Set rToCopy = wbSource.Worksheets(1).Range("B2", wbSource.Worksheets(1).Range("B" & Rows.Count).End(xlUp)).Resize(, 20)
                        Set rNextCl = wbThis.Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
                        If bHeaders Then
                            'headers exist so don't copy
                            rToCopy.Offset(1, 0).Resize(rToCopy.Rows.Count - 1, _
                                                        rToCopy.Columns.Count).Copy rNextCl
                            'no headers so copy
                            'place headers in Row 2
                        Else: rToCopy.Copy wbThis.Worksheets(1).Cells(2, 2)
                            bHeaders = True
                        End If
                        wbSource.Close False     'close source workbook
                    Next lCount
                Else: MsgBox "No workbooks found"
                End If
            End With
     
            On Error GoTo 0
            .ScreenUpdating = True
            .DisplayAlerts = True
            .EnableEvents = True
        End With
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,205

    Re: Collate data from multiple workbooks/sheets into master workbook

    umy,

    Welcome to Ozgrid.

    Please do not use non-essential words or phrases in your thread title, such as "Help!", "Macro to ...", or other like "Urgent", "Important", etc.
    Keep your thread title concise and only use terms that accurately describe the thread content, avoiding use of words that do not facilitate searches.

    Thanks.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  9. #9
    Join Date
    16th August 2010
    Posts
    27

    Re: Help! - Macro to collate data from multiple workbooks/sheets into master workbook

    Hi

    Unfortunately that didn't work, it only copied and pasted the headings in Row 2, nothing else.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    16th August 2010
    Posts
    27

    Re: Collate data from multiple workbooks/sheets into master workbook

    Hi

    Ignore my last post, it seems to have worked a treat, my machine just needed restarting.

    The only thing missing now is Column A.

    Thank you sooo much! :-)

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 7
    Last Post: February 19th, 2013, 14:07
  2. Replies: 4
    Last Post: July 20th, 2010, 20:39
  3. Replies: 6
    Last Post: June 8th, 2008, 11:09
  4. Copying Data To Multiple Workbooks From A Master
    By John Gatehouse in forum EXCEL HELP
    Replies: 6
    Last Post: June 15th, 2007, 03:26
  5. Merge / Collate Workbooks into 1 Workbook
    By hassesbar in forum EXCEL HELP
    Replies: 2
    Last Post: October 18th, 2005, 23:03

Bookmarks

Posting Permissions

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