I’ve mentioned before I’m a big fan of tables in Excel 2010. One way I use them is in models, where each table represents a different scenario. The models let users create new scenarios, based either on an existing one or a blank template. Each version is stored in a table in its own worksheet. These tables always have the same fields (columns) but the values in the variable fields are different. The number of rows can differ from table to table.
I often want to copy, in VBA, the contents from a “source” to a “target” table. If I just copy the whole thing, the target table will be overwritten and renamed – something like “tblSource1″ (adding a “1” to the source table name). That breaks any formulas referring to “tblTarget.” They’ll show a #REF error because they can’t find “tblTarget.” So I need code that copies the table data from tblSource to tblTarget without completely replacing tblTarget.
I’ve been writing code on a case-by-case basis, but thought that I’d generalize it a bit more. In addition to keeping the table’s identity intact, it should copy the source’s totals row if there is one, and turn off the target’s total row if there isn’t. The number of rows should increase or decrease to match the source. And, although I’ve only ever copied tables as values, I want the option to copy formulas.
I thought about dealing with a different number of columns but, at least in my uses so far, that shouldn’t happen. If I ever do try to accommodate models with changing numbers of fields, I think I’d do some testing before ever calling this code, and adjust the headers in another procedure.
So here’s what I came up with:
Dim FormulaCells As Excel.Range
If .DataBodyRange.Rows.Count <> loSource.DataBodyRange.Rows.Count Then
'have to clear target otherwise old table content may be outside new table
'set target rows count to source rows count
.Resize .Range.Cells(1).Resize(loSource.HeaderRowRange.Rows.Count + _
If CopyFormulas Then
On Error Resume Next
Set FormulaCells = .DataBodyRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
'if yes, then replace any references to source table with target
If Not FormulaCells Is Nothing Then
FormulaCells.Replace what:=loSource.Name, replacement:=.Name, lookat:=xlPart
.DataBodyRange.Value2 = .DataBodyRange.Value2
'turn target Totals row on or off to match Source
If loSource.ShowTotals Then
.ShowTotals = True
.ShowTotals = False
One thing I learned is that there are two Resizes in a table (listobject). The first type, the Range property, was familiar, e.g.,
… which yields a range object whose address is A1:A20.
The second is the Listobject.Resize method, which allows you to modify a table’s range, e.g.,
which will change loTarget’s range to A1:F20.
Both of these types of Resizes are used in the code above, in the same line, happily enough.