How can I use range objects to solve this problem

  • Thread starter Thread starter shishi
  • Start date Start date
S

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
 
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
 
Back
Top