Consulting

Results 1 to 11 of 11

Thread: I need help merging workbooks (1 worksheet in each) into a summary workbook.

  1. #1
    VBAX Regular
    Joined
    May 2014
    Posts
    7
    Location

    I need help merging workbooks (1 worksheet in each) into a summary workbook.

    Hi Folks, First post so as usual please be gentle for any mistakes in my posting....

    I have gotten code to take worksheets from mutiple workbooks and insert these sheets into one worksheet - so in theory say i have twelve workbooks for each month of the year. I would like to merge the information from these 12 sheets into a separate workbook but on the same worksheet. As in Jan information, followed by Feb information etc etc.

    The problem is that the code i have got does not seem to add the information each time a worksheet is opened in the sode so i only end up with what i think is the information in the last worksheet opened. I apologise for having to be vague but I cannot go into details of the data being used. Here is the code i have gotten so far. Hopefully someone can spot probably the obvious mistake I have done.

    I would just like to stress I am no expert in VBA, been a few years since I used it in school....

    Sub consolidate()
    Dim sh As Worksheet, lr As Long, fPath As String, wb As Workbook, sh2 As Worksheet, fNm As String
    Dim lstRw As Long, rng As Range
    Set sh = Sheets(2)
    lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    fPath = "folder with the mutiple workbooks in it"
    If Right(fPath, 1) <> "\" Then
    fPath = fPath & "\"
    End If
    fNm = Dir(fPath & "*.xl*")
    Do
    lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    Set wb = Workbooks.Open(fPath & fNm)
    Set sh2 = wb.Sheets(1)
    lstRw = sh2.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = sh2.Range("A2:S2")
    rng.EntireRow.Copy sh.Range("A" & lr + 1)
    wb.Close False
    fNm = Dir
    Loop While fNm <> ""
    End Sub


    Thank you all for even taking the time to read this. And also thanks in advance if you help me out.

    Last edited by Bob Phillips; 05-13-2014 at 02:14 AM. Reason: Added VBA tags

  2. #2
    VBAX Regular
    Joined
    May 2014
    Posts
    7
    Location
    I should further clarify, that in these workbooks the sheet with the information on it has all the same fields, From A to S. Starting at A2 since i will have the title of the columns already filled in. Hope this makes it clearer.

  3. #3
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Greetings Mac,

    My Mom's side are "Hunter"s. I have never been, but would love to visit Ireland.

    Might I respectfully suggest that if you wish to learn, you would want to put more effort into it. Simply put, create the workbooks needed (same type data in the same 'places') and a workbook showing the desired results. It will be some work on your part.

    Zip these workbooks and attach the zip with the explanation.

    Mark

  4. #4
    VBAX Regular
    Joined
    May 2014
    Posts
    7
    Location

    Hopefully this makes a little more sense

    Thanks Mark for the advice.
    So ok I have attached a Zip. 3 Books that each contain one sheet that i would like combined in the first sheet in the Master Summary book. The problem is that it seems to open them all and put them in but it seems to over write the data from the previous workbook and not add it to the next row.

    I know i only created 3 wb's and a Master Summary but the data in them reflect the data i would to be pulled from each. (In reality it will be a lot more than 3 wb...)

    Again sorry if i have forgotten to do anything or if i have done something extremely stupid.

    Thanks

    Mac
    Attached Files Attached Files

  5. #5
    Methinks this line:

    lstRw = sh2.Cells(Rows.Count, 1).End(xlUp).Row

    should be changed to:

    lstRw = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  6. #6
    VBAX Regular
    Joined
    May 2014
    Posts
    7
    Location
    Hi Jan, Thanks for the input. Now the problem is very clear to me. The code is only taking the first line from each workbook and placing it in order in the Master Summary. So all i have to do is figure out how to copy ALL the rows in each workbook.

    Thanks again folks. If I get this sorted, you will have saved me.

  7. #7
    VBAX Regular
    Joined
    May 2014
    Posts
    7
    Location
    If anyone has any thoughts as to how to fix this problem it would be very much appreciated. Or if i need to do more to explain further please let me know.

  8. #8
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub Button1_Click()
       With Application.FileDialog(msoFileDialogFilePicker)
          .AllowMultiSelect = True
          .Filters.Add "Excelsior", "*.xl*"
          .Show
    
          For j = 1 To .SelectedItems.Count
             With GetObject(.SelectedItems(j))
               With .Sheets(1).UsedRange.Offset(1).Resize(, 19)
                  Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count) = .Value
               End With
               .Close 0
               End With
          Next
    
        End With
    End Sub

  9. #9
    VBAX Regular
    Joined
    May 2014
    Posts
    7
    Location
    snb you are a life saver. I know i have to select the files in the folder manually but compared to what you would normally have to do, this is fantastic. Thanks very much and thanks to everyone else who helped.

    Sorted

  10. #10
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I hope you are aware you can select all the files you need to be processed in one go.

  11. #11
    VBAX Regular
    Joined
    May 2014
    Posts
    7
    Location
    ha yeah i know that im not that green. I just meant that you actually had to select files rather than it automatically doing it. Anyway Thanks a million.

Tags for this Thread

Posting Permissions

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