Hi

Create yourself a new sheet called "All Data" and set up a header row to
match your other sheets.
Then, use the following code to create the summary.
Run the code whenever you have new data in any of your sheets, as it will
first clear the Summary sheet, then rebuild the data.

Sub combinesheets()
Dim sht As Worksheet, SumSht As Worksheet
Dim NewRow As Long, LRow as Long

Application.ScreenUpdating = False

NewRow = 2
Set SumSht = Sheets("All Data")
SumSht.Range("2:65536").Delete

For Each sht In ThisWorkbook.Sheets
If sht.Name < "All Data" Then

' this is set to copy columns A to K from each sheet
' and to place the source sheet name in column L
' Amend column letters to suit your requirement

LRow = sht.Range("A" & Rows.Count).End(xlUp).Row
sht.Range("A2:K" & LRow).Copy SumSht.Range("A" & NewRow)
SumSht.Range("L" & NewRow & ":L" & NewRow + LRow - 2) = sht.Name
NewRow = NewRow + LRow - 1
End If

Next sht

With SumSht
Columns("A:L").EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
End With
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
InsertModule
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up Macros
Highlight the macro name
Run


--
Regards
Roger Govier

"Roachy" wrote in message
...
Guys - Im really struggling with this on..

I am receiving 7 submission from different areas which I copy into
different
tabs in a central workbook that I manage, what im looking to do is have a
consolidated view so a sheet that will pull all of the data from each
individual tab into one full list.

The problem I have is that the individual submission are constantly
growing
in size so I am unable to reference to particular cells without leaving
big
gaps in the summary tab