Reposting to get some answers......

[shishi]

Hi all,


I have couple of tables that can have many number of rows. The
number of rows can vary depending on the number of items that I need to

put into each table. The last row of the table is always will be "Total

and some number".


The work sheet contains may items and the above tables. The number of

tables could be anywhere from 1-5.


I am trying to generate a vba code that can copy only the tables from

this worksheet and then assort all the tables to new a worksheet with
an empty row in between the tables.


Is there a way to use range objects to get this done? How can we
specify a range object that is between the start of the table to the
end of the table?


Thanks in advance for all the suggestions.
shi

[Tom Ogilvy]

Dim sh as Worksheet, rng as Range
Dim ar as Area
set rng = ActiveSheet.UsedRange.SpecialCells(xlConstants)
set sh = Worksheets("NewSheet")
for each ar in rng.areas
if application.Countif(ar,"*Total*") > 0 then
ar.Resize(ar.rows.count-1).Copy _
destination:=sh.Cells(rows.count,1).end(xlup)(3)
end if
Next
sh.Range("1:2").Delete

Tables must be separated from other data by at least on blank row/column on
each side (or be on the edge of the sheet)

--
Regards,
Tom Ogilvy

"shishi" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
>
> I have couple of tables that can have many number of rows. The
> number of rows can vary depending on the number of items that I need to
>
> put into each table. The last row of the table is always will be "Total
>
> and some number".
>
>
> The work sheet contains may items and the above tables. The number of
>
> tables could be anywhere from 1-5.
>
>
> I am trying to generate a vba code that can copy only the tables from
>
> this worksheet and then assort all the tables to new a worksheet with
> an empty row in between the tables.
>
>
> Is there a way to use range objects to get this done? How can we
> specify a range object that is between the start of the table to the
> end of the table?
>
>
> Thanks in advance for all the suggestions.
> shi
>