Stacking Data/Combining Sheets

[Prometheus_au]

G'day all,

New to all this so go easy on me!
I have an Excel Workbook with 50 sheets. I'd like to "stack" (I think thats the right term) data from each sheet into one. Luckily the data is always in the same place on each sheet (B6:F30).

Is there a way to achieve this?

Thanks all....

 

[parry]

Presuming your wanting to copy to a new sheet pasting into cell A1 downwards then this is hopefully what you want.

Sub CopyData()
Dim NewSh As Worksheet, Sh As Worksheet, NxtRow As Long

'Create a new sheet to store the combined details
Set NewSh = ThisWorkbook.Sheets.Add
NewSh.Name = "CombinedData_" & Format(Date, "DDMMYYYY")

For Each Sh In ThisWorkbook.Sheets
If Sh.Name <> NewSh.Name Then
    If NxtRow < 1 Then
        NxtRow = 1
    Else
        NxtRow = NewSh.Range("A65536").End(xlUp).Row + 1
    End If
    Sh.Range("B6:F30").Copy NewSh.Cells(NxtRow, 1)
End If
Next Sh

End Sub

 

[Prometheus_au]

Thanks heaps....

Exactly what I needed.


You've saved me hours of manual cutting and pasting.

 

[parry]

My pleasure.