Copy Table Data While Not Breaking References

Copy Table Data While Not Breaking References

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.

Coffee Model

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:

Sub CopyTableData(loSource As Excel.ListObject, loTarget As Excel.ListObject, Optional CopyFormulas As Boolean = False)
Dim FormulaCells As Excel.Range

With loTarget
    If .DataBodyRange.Rows.Count <> loSource.DataBodyRange.Rows.Count Then
        'have to clear target otherwise old table content may be outside new table
       .DataBodyRange.Cells.Clear
        'set target rows count to source rows count
       .Resize .Range.Cells(1).Resize(loSource.HeaderRowRange.Rows.Count + _
                                       loSource.DataBodyRange.Rows.Count, loSource.Range.Columns.Count)
    End If
    loSource.DataBodyRange.Copy Destination:=.DataBodyRange.Cells(1)
    If CopyFormulas Then
        On Error Resume Next
        'any formulas?
       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
        End If
    Else
        .DataBodyRange.Value2 = .DataBodyRange.Value2
    End If

    'turn target Totals row on or off to match Source
   If loSource.ShowTotals Then
        .ShowTotals = True
        loSource.TotalsRowRange.Copy Destination:=.TotalsRowRange
    Else
        .ShowTotals = False
    End If
End With

End Sub

One thing I learned is that there are two Resizes in a table (listobject). The first type, the Range property, was familiar, e.g.,

Range("A1").Resize(20,1)

… 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.,

loTarget.Resize(Range("A1:F20")

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.

16 thoughts on “Copy Table Data While Not Breaking References

  1. Hi Doug. Puzzle for you: How would you copy Excel 2010 table(listobject) INCLUDING hidden/filtered rows?

    As per a question that I’ve posted over at http://www.mrexcel.com/forum/excel-questions/668968-copy-excel-2010-table-listobject-including-hidden-filtered-rows.html#post3314710
    I’m trying to copy a whole bunch of tables with identical layout to a master table, so I can create a whole bunch of pivot tables that include data from ALL the various tables.

    The source tables MAY be filtered, and I can’t work out how to copy them easily while also INCLUDING any hidden/filtered rows while at the same time leaving any filter settings on the source tables intact.

    If I use something like range(“Table1″).Listobject.DataBodyRange.Copy then it only copies the VISIBLE rows. But I want ALL rows to be copied to a master table. And there is no range(“appHook_Personnel”).Listobject.Copy method.

    1. I don’t want to unfilter the sources tables if I can help it, because users might still want the source tables to remain exactly as the user filtered them. (However, it doesn’t matter if the DESTINATION list is filtered or not). I realise that I could copy the entire sheet to a temp sheet, then unfilter any tables on that sheet and THEN copy these to the master list. But wan’t to know if there’s a simpler way.

    2. I DON’T want to use SQL to create a pivottable directly from the tables, because the tables will have further information added to them from time to time, and so if I use SQL to make a pivot directly from them, I’ll have to recreate the pivotcache using that SQL query each time, which might muck up the settings in any existing pivottables. I realise that I could use SQL to copy the data to a ‘staging area’, and just point the pivot table at that. But again I was looking for a simpler way if possible.

    3. I can’t use PowerPivot, because its not installed in this environment.

    What would you do?

    • Jeff, I can’t duplicate the behavior.

      When I copy a DataBodyRange, the whole thing gets copied, even though it’s filtered. I’ve tried copying to the same sheet, a different one in the same workbook and in a different workbook. I did two tables side-by-side. I tried both DataBodyRange and Range(“Table1[#Data]“). Also with Copy using the Destination argument and Copy followed by Paste or PasteSpecial. It all works, i.e., a filter is applied within the table(s) but all rows are copied.

      Can you give me a simple example with it not working?

  2. Ahhh…I’m suspecting you didn’t have formulas in your table?

    When my table has numbers, I get your result.

    When my table has a whole bunch of formulas i.e. =RAND() then I get my result.

    Interesting.

    • That’s it. I only had to change one number to a simple formula “=2*1″ and now only the unfiltered rows are copied.

      That’s if I use Copy with the Destination argument. If I use Copy with PasteSpecial>Values, it copies all the rows. Does that work for you?

      • I don’t think formulas has ANYTHING to do with it. Rather, It seems you get inconsistent depending on whether you just manually copied something to the clipboard. Which is very good to know. And very bad behaviour.

        Try this.

        In Cell A1 put “Some Heading”
        In Cell A2 to A11 put =ROW()-1
        Turn it into a table
        Hide every 2nd row

        Manually select cells A1:A11 and copy them via [CTRL] + [C ]. Notice that the marching ants effect shows that it’s just copying the cells you can see.

        Paste into A20. Only the visible cells from the original list are pasted.

        Now run this macro on the same sheet:

        Sub test()
        Range("A1").ListObject.DataBodyRange.Copy
        Range("A20").PasteSpecial xlPasteValues
        End Sub

        The first time you run it, it just pastes the visible cells from the original list.

        On subsequent passes, it pastes the whole range.

        But if you manually copy the range again, then on the next pass the macro again just pastes the visible cells from the original list.

        That’s pretty crappy. And putting Application.CutCopyMode = False at the top of the code makes no difference.

        So it seems that you can never guarantee what you are copying when you are copying a filtered list.

        Whether it’s an Excel table or not makes no difference.

        • It definitely behaves differently with a manual copy and pastes only the visible cells.

          However, when I follow your steps above exactly, it pastes the entire contents of the Databody range the first and every time I run the code.

          More generally, the solution I’d probably use is to make a copy of the table and work with that, as you suggest in #1 above.

          • So if you run the macro immediately after manual copying, it copies and pastes the entire table both times?

            Ha. What did you do right in a past life to deserve such consistency? ;-)

            Hey, where abouts are you based, Doug? You’re only a couple of hours ahead of me, looking at the timestamps above. I’m in New Zealand.

  3. Great code!

    I’m trying to adapt it to copy table data from another workbook into the current workbook (and I don’t have formulas or total rows to deal with).

    I’m getting a “Object variable or with variable not set” for the With loTarget part of “If .DataBodyRange.Rows …” Any idea what I’m doing wrong?

    Dim wb As Workbook

    Application.ScreenUpdating = False
    Set loTarget = ActiveSheet.ListObjects("tblMyData")

    ' open the source workbook, read only
    Set wb = Workbooks.Open("C:\Stuff\NewData.xlsx", True, True)
    Set loSource = wb.Worksheets("UpdatedData").ListObjects("tblMyData")

    With loTarget
           If .DataBodyRange.Rows.Count <> loSource.DataBodyRange.Rows.Count Then
           .DataBodyRange.Cells.Clear
           .Resize .Range.Cells(1).Resize(loSource.HeaderRowRange.Rows.Count + _
                                           loSource.DataBodyRange.Rows.Count, loSource.Range.Columns.Count)
        End If
        loSource.DataBodyRange.Copy Destination:=.DataBodyRange.Cells(1)        
        End With
        wb.Close False
        Set wb = Nothing
        Application.ScreenUpdating = True

    Also, since my tables are named (in both workbooks), do I need to reference the worksheets in my Set statements? I couldn’t find VBA examples of referring to table that didn’t include the worksheet name.

    I actually need to replace data from three tables in my active workbook with data from three tables in my NewData workbook. So I might be looking at creating loSource1, 2, and 3, and loTarget1, 2, and 3.

    • Thanks! I’d guess that your target table has no rows in it yet, which will mean it has no DataBodyRange. I’d just add a row or two (a blank first row doesn’t always get recognized, I think).

      I’d continue to use worksheet names in setting the listobjects. For another approach,take a look at my post on creating a workbook-level table class. You don’t say what version of Excel, but this will only work in versions after 2003, where table names have to be unique across a workbook.

      Rather than create three loSources, I’d figure a way to loop through the relevant tables and assign them to loSource and loTarget and run your code. So basically, wrap the code you’ve got in a loop. That will be more maintainable if, for example, you add another table or another pair of workbooks.

      • > Rather than create three loSources, I’d figure a way to loop through the relevant tables and assign them to loSource and loTarget and run your code …

        Been trying to figure this out. Almost got it but the loSource variable is getting set = to “Nothing”, which is causing an error. Any ideas?

        The target and source workbooks all have the same sheet names and same table names (but different indexes so I’m using an array with table names hard coded rather than referring to the sheets’ index) Each sheet also has only one table on it.

        Sub CopyTableData()

        Dim wb As Workbook
        Dim CurrentTable As ListObject
        Dim CurrentWorksheet As Worksheet
        Dim loTarget As Excel.ListObject
        Dim loSource As Excel.ListObject
           
        Application.ScreenUpdating = False
           
        ' open the source data, read only
        Set wb = Workbooks.Open("C:\Stuff\NewData.xlsx", True, True)
           
        ' move focus back to target workbook
        Workbooks("Old Data.xlsm").Activate
           
        For Each CurrentWorksheet In Sheets(Array("SheetABC", "SheetXYZ"))
               
           Set loTarget = CurrentWorksheet.ListObjects(1)
           MsgBox loTarget.Name
           ' object doesn't support this property or method (loSource = "Nothing")
           Set loSource = wb.CurrentWorksheet.ListObjects(1)    
           MsgBox loSource.Name
                         
        With loTarget
           ...
        End With

        Next CurrentWorksheet

          wb.Close False ' close source workbook
          Set wb = Nothing
          Application.ScreenUpdating = True

        End Sub
        • The problem is the wb prefix:

          Set loSource = wb.CurrentWorksheet.ListObjects(1)

          won’t work because the Workbook object doesn’t have a CurrentWorksheet property.

          So you need to create two sheet arrays (a target and a source) and be sure to assign them to the correct workbooks. In general, you want to qualify collections, e.g., you don’t want to just say “Sheets”, rather SomeWorkbook.Sheets.

          Or, if I’m understanding your code, you could do something like:

          Set loSource = wb.Sheets(CurrentWorksheet.Name).Listobjects(1)

          But I think that’s more confusing.

          • That did the trick! The nice thing about this approach is, assuming the source and target workbooks use the same sheet names, you don’t need to specify two sheet arrays.

            You can specify the worksheet names once in the For Each line, and for loSource use CurrentWorksheet.ListObjects(1) and for loTarget use wb.Sheets(CurrentWorksheet.Name).Listobjects(1)

            You also don’t even need to know the names of the tables! This makes the code very flexible.

  4. Hi, this is an interesting procedure.

    I’m currently trying to call this procedure for a pair of tables which have an autonumbered column 1, as follows:

    =ROW(A1)

    However, I obviously get the REF! error in the target table, as the replace method does not find loSource.Name to replace with loTarget.Name.

    Could anyone advise the best way to ensure that column 1 of the target table remains numbered correctly?

    Much appreciated.
    Brian

    • I think the “#REF” error has a different cause. That line of code that replaces one table’s name with the other wouldn’t change anything in the “=ROW(A1)” formula that you describe, since it doesn’t include the source table’s name.

      If that doesn’t help, I’d suggest posting your specific example, and the code, on a forum like StackOverflow. You’ll probably get a faster solution than here. Good luck.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>