Announcement

Collapse
No announcement yet.

Collate data from multiple workbooks/sheets into master workbook

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 Files
    Last edited by AAE; August 16th, 2010, 23:21. Reason: revise thread title

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

    Please post workbooks, not pictures.

    Comment


    • #3
      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

      Comment


      • #4
        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?

        Comment


        • #5
          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 :-)

          Comment


          • #6
            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

            Comment


            • #7
              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

              Comment


              • #8
                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

                Comment


                • #9
                  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.

                  Comment


                  • #10
                    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! :-)

                    Comment


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

                      Verified my testing by placing 3 test files in the directory.

                      The macro doesnt copy and paste the last row from files 1 and 3, but does copy everything from file 2.

                      Comment


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

                        That's rather odd. Please can you attach your files?

                        Comment


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

                          Firstly, thank you so much, your solution is working great so far.
                          I have re-tested with clean data and it looks like the missing data was due to certain cells not containing any data in column b, so not a major one to worry about.

                          The only thing that I need now is the column A data to come across and then everything will be perfect.
                          One other question if I may, is that what amendments do i need to make to the macro to read the other sheets in the workbook? at present, it only reads the first sheet.

                          I would also like to carry out the same actions for sheets 2 and sheets 3 of the workbook, i.e. it copies all of the data on the respective sheets from the other workbooks, just as it current does with sheet 1, but instead of placing it into sheet 1, i'd like it to correspond so data from the other sheets gets pasted into the corresponding sheets in the master.

                          Many thanks once again
                          Last edited by umy; August 17th, 2010, 17:29. Reason: Duplication

                          Comment


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

                            Please see above for the final part of the project, almost finished now :-)

                            Comment


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

                              Hi Stephen R, if you are still out there, please help to finish this by bringing in column A.

                              Thank you :-)

                              Comment

                              Working...
                              X