Function or Code to "Append" rows in worksheet

[Maverick27]

Hi - does anyone know of a quick solution either (Code of Function) to "APPEND" the rows of ALL other Worksheets into Table1 (Worksheet1 ) ?
See imageshack link below:
http://imageshack.us/photo/my-images/263/excelf.jpg/

In short, i want to append the DATA from other Worksheets (shown in Blue box) into Worksheet1 (Red Arrow).

All worksheets have same number of Columns.

Thanks - Mave

 

[Peter_SSs]

See if this does what you want. Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ConsolidateData()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet, wsC <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> nr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">'Name of consolidation sheet</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> Consol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Table 1"<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsC = Sheets(Consol)<br>    nr = wsC.UsedRange.Rows.Count + 1<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>        <SPAN style="color:#00007F">With</SPAN> ws<br>            <SPAN style="color:#00007F">If</SPAN> .Name <> Consol <SPAN style="color:#00007F">Then</SPAN><br>                .UsedRange.Copy Destination:=wsC.Cells(nr, 1)<br>                nr = nr + .UsedRange.Rows.Count<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> ws<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Maverick27]

Thanks - works beautifully.