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
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.,
… 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.
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?
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:
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.
It’s 5:15 pm here, so I probably should be awake, given I got into work a bit late 😉
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?
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.
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:
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:
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.
Glad to hear it!
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:
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.
Hey, I just wanted to say thanks – this saved me a lot of trial & errors!