To summarise the data on the sheets in the same workbook.

summarisingSheetData.bas

Sub summarisingSheetData()
	Dim wb As Workbook
	Dim i As Long, j As Long
	Dim summarySheet As Worksheet

	Set wb = ThisWorkbook
	Set summarySheet = Worksheets.Add(before:=wb.Worksheets(1)) 
	summarySheet.Name = "Summary"

	'Headingを設定。
	With summarySheet
		.cells(1, 1).Value = "事務所コード"
		.cells(1, 2).Value = "事務所名"
		.cells(1, 3).Value = "氏"
		.cells(1, 4).Value = "名"
		.cells(1, 5).Value = "氏（ふりがな）"
		.cells(1, 6).Value = "名（ふりがな）"
		.cells(1, 7).Value = "到着日"
		.cells(1, 8).Value = "出発日"
		.cells(1, 9).Value = "アレルギー"
		.cells(1, 10).Value = "（緊急連絡用）携帯電話"
	End With

	For i = 2 to wb.Worksheets.Count
		For j = 1 to 10
			summarySheet.cells(i, j).Value = wb.Worksheets(i).cells(2 * j + 3, 5).Value
		Next j
	Next i

	MsgBox "DONE"
End Sub

'another way
Sub summarisingSheetData_2()
	Dim wb As Workbook
	Dim i As Long, j As Long
	Dim summarySheet As Worksheet
	Dim arrHeader As Variant

	Set wb = ThisWorkbook
	Set summarySheet = Worksheets.Add(before:=wb.Worksheets(1)) 
	summarySheet.Name = "Summary"

	'Headingを設定。
	arrHeader = Array("事務所コード", "事務所名", "氏", "名", "氏（ふりがな）", "名（ふりがな）", _
					"到着日", "出発日", "アレルギー", "（緊急連絡用）携帯電話")
	summarySheet.Range("A1:A10") = arrHeader

	For i = 2 to wb.Worksheets.Count
		For j = 1 to 10
			summarySheet.cells(i, j).Value = wb.Worksheets(i).cells(2 * j + 3, 5).Value
		Next j
	Next i

	MsgBox "DONE"
End Sub