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.

 

[Peter_SSs]

ferijen

You could give this a go as a starting point.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CombineSheets()
    <SPAN style="color:#00007F">Dim</SPAN> wsC <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> nr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Combined"
    <SPAN style="color:#00007F">Set</SPAN> wsC = Sheets("Combined")
    nr = 1
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        <SPAN style="color:#00007F">If</SPAN> ws.Name <> wsC.Name <SPAN style="color:#00007F">Then</SPAN>
            lr = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Range("A1:H" & lr).Copy _
                Destination:=wsC.Cells(nr, 1)
                nr = nr + lr
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>