Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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... :-)

    VB:
    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,057

    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,057

    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,057

    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.
    VB:
    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,206

    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