Is there a way to consolidate 30 spreadsheets into one for data entry?

[ggremel]

For example, lets say you are working on 3 files with 14, 20, and 21 lines. These ranges are consistently being increased with new activity. Let say, they each received 4 new rows of data.

How can you consolidate them without having blanks rows.

Thanks

[KiPA]

Definitely a place for a macro.

In my example there is a theoretical max of 50 rows (which was easier to record than with something like 500 rows) in each of the three lines but you can see in the code where to modify when needed.

So Sheet1 is the master sheet to collect the data and Sheets from 2 to 4 are the information sheets. Information (with blanks in the end) is A1:A50 in every sheet and it's consolidated manually (not with Excel Consolidate feature). I believe the code makes things pretty clear:

Sub Macro1()
    Sheets("Sheet2").Select
    Range("A1:A50").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Sheet3").Select
    Range("A1:A50").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A51").Select
    ActiveSheet.Paste
    Sheets("Sheet4").Select
    Range("A1:A50").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A101").Select
    ActiveSheet.Paste
    Range("A1:A150").Select
    Range("A150").Activate
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    Range("A1").Select
End Sub

Indeed I recorded the macro but not wrote it so there might be pointless lines. Shouldn't be though.

Again: this needs to be expanded from 3 to 30 sheets and the data ranges might need a slight modification.

---
Edit: Amazed by code below

[JBeaucaire]

To collate every sheet except sheet1 (which is the summary itself), try this:

Option Explicit

Sub Consolidate()
Dim ws As Worksheet
Dim Summary As Worksheet

Set Summary = Sheets("Sheet1")
Summary.Cells.Clear

For Each ws In Worksheets
    If ws.Name <> Summary.Name Then _
        ws.Range("A1", ws.Range("A" & Rows.Count).End(xlUp)).Copy _
            Summary.Range("A" & Rows.Count).End(xlUp).Offset(1)
Next ws

Summary.Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub