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....

Code:

---

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.

:)

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.

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

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

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

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. :)

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.

Code:

---

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

---

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 :D

I hope you are aware you can select all the files you need to be processed in one go.

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.