How can I use range objects to solve this problem

[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

[Dave Peterson]

If each of the tables is separated from the others (by blank rows/columns), then
maybe...

Option Explicit
Sub testme()

Dim myArea As Range
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim destCell As Range
Dim myRng As Range
Dim ConstRng As Range
Dim FormRng As Range

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

Set destCell = newWks.Range("a1")

With curWks
On Error Resume Next
Set ConstRng = .Cells.SpecialCells(xlCellTypeConstants)
Set FormRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If ConstRng Is Nothing Then
Set myRng = FormRng
ElseIf FormRng Is Nothing Then
Set myRng = ConstRng
Else
Set myRng = Union(FormRng, ConstRng)
End If

For Each myArea In myRng.Areas
myArea.Copy
destCell.PasteSpecial Paste:=xlPasteValues
Set destCell = destCell.Offset(myArea.Rows.Count + 1, 0)
Next myArea

End With

End Sub

shishi wrote:
>
> 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

--

Dave Peterson