Combining data in multiple sheets into one sheet

[ferijen]

I have a multi-sheeted workbook (about 80 sheets I think) where every sheet contains data in the same layout & format: 8 columns wide, although each sheet varies in the number of rows.

I'd like to combine all this data so it is on one sheet - essential a copy/paste action for each sheet in the workbook - but I wondered if anyone else had already written the vb code which would make my task easier? I know that I won't exceed the maximum number of rows when it is all put on to one sheet.

 

[ravishankar]

Hi
paste the following codes in the macro window ( Alt F11)

Sub collate()
Sheets.Add.Name = "summary"
For a = 2 To Sheets.Count
x = Worksheets(a).Cells(Rows.Count, 1).End(xlUp).Row
d = Worksheets("summary").Cells(Rows.Count, 1).End(xlUp).Row
worksheets("summary").cells(d,1) = worksheets(a).name
b = "A1:H" & x
Worksheets(a).Range(b).Copy
Worksheets("summary").Cells(d + 2, 2).PasteSpecial
Next a
End Sub

run the macro. It will copy A to H from each sheet and pastes to summary sheet.
Ravi