Try the following code placed in a standard module and assigned to a button:-
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name <> "Summary" Then
Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
I'm assuming that you would like all the data from each sheet consolidated into the Summary sheet (Sheet1).
The above code will do this regardless of the number of sheets in your workbook. The code will first clear any existing data in the Summary sheet prior to any new data being transferred over.
I hope that this helps.