Merge spreadsheets by different row quantity

[snapz]

I have 5 workbooks. We'll call them code1, code2, code3, code4, code5. I need to merge these into one workbook called master. All workbooks only have one sheet named Sheet1. I need to pull 50 records (rows) from code1, 63 records (rows) from code2, 50 records (rows) from code3, 38 records (rows) from code4, 50 records (rows) from code5 and repeat the process until all the records are merged. Is this even possible?

Thanks. TJ

 

[mumps]

Are all the workbooks already open or are they closed? Does the sheet in the source workbooks have a header in row 1? If it does, do you want the header to be copied followed by the number of set rows? Do the 5 workbooks have an "xlsx" extension?

 

[snapz]

Workbooks can be open or closed. Whichever is easier. Right now they are closed. Each sheet has a header row. It doesn't need to be copied, I can delete it. Yes they have an xlsx extension. Also each workbook has 3 columns a,b,c.

 

[mumps]

Copy/paste this macro into a regular code module in your "Master" workbook. I am assuming that your destination sheet in the Master is named "Sheet1". Save the Master as a macro-enabled file so its extension changes to "xlsm". Make sure that the other workbooks are all open. You don't have to delete the header rows. Change the file names in the code to match your actual file names.

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook
    For Each srcWB In Application.Workbooks
        If srcWB.Name <> "Master.xlsm" Then
            Select Case srcWB.Name
                Case "code1.xlsx", "code3.xlsx", "code5.xlsx"
                    srcWB.Sheets("Sheet1").Rows("2:51").EntireRow.Copy Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                Case "code2.xlsx"
                    srcWB.Sheets("Sheet1").Rows("2:64").EntireRow.Copy Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                Case "code4.xlsx"
                    srcWB.Sheets("Sheet1").Rows("2:39").EntireRow.Copy Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            End Select
        End If
    Next srcWB
    Application.ScreenUpdating = True
End Sub

 

[snapz]

Named all my workbooks accordingly. Ran the macro. Get a run-time error '9': script out of range. Debugger highlights

srcWB.Sheets("Sheet1").Rows("2:51").EntireRow.Copy Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

under the Case "code1.xlsx", "code3.xlsx", "code5.xlsx"

 

[snapz]

Nevermind. I had a copy/paste failure. The macro works. Sort of. It doesn't keep repeating the macro until the end of the workbooks. It only gives me one set. Is there a way to make it repeat until the end of the workbooks?

 

[mumps]

I tried it on some dummy workbooks and it worked properly. Are they all open? Check that the names are correct.

 

[snapz]

It did work. It was my error. Now I need the macro to loop until it gets to the first empty row of code1.xlsx (or any workbook if it's easier that way)

 

[mumps]

Do you mean you want to copy all the rows in each of the 5 workbooks up to the first empty row?

 

[snapz]

Yes. Currently my test documents have enough data to loop the macro 10 times. My project will be considerably more. I'd like the macro to loop until it gets all of the data into the master document.