Here’s a cautionary tale for you. Let’s say you want to let users copy rows from a Source table row by row to a Dest table, by pushing a button:
…so you whip up a bit of code like this, and assign it to that button:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Sub CopyListRows() Dim loSource As ListObject Dim lrSource As ListRow Dim loDest As ListObject Dim lrDest As ListRow Set loSource = Range("Source").ListObject Set loDest = Range("Dest").ListObject On Error Resume Next 'LoDest might have no data loDest.DataBodyRange.Delete On Error GoTo 0 For Each lrSource In loSource.ListRows Set lrDest = loDest.ListRows.Add lrSource.Range.Copy lrDest.Range Next lrSource End Sub |
And when you run it, it works just fine:
…until that is, someone hides an entire column in the source table, sets a filter that similarly hides some rows, and leaves a cell in that Table selected before running the code again, in which case you get this:
As you can see from the above:
- For any ListRows in your source that happen to be visible, only the cells from the visible columns get copied, to a contiguous block in the Dest table, but
- For hidden ListRows, all cells get copied
Add to this the fact that everything works just fine if the user happened to select a cell outside the table before triggering the code:
…and you’ve got the makings of a hard-to-diagnose bug that will eat up hours of your time trying to replicate.
The fix? Don’t use the .copy method. Just set the values of the second range directly to the values of the first:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Sub CopyListRows() Dim loSource As ListObject Dim lrSource As ListRow Dim loDest As ListObject Dim lrDest As ListRow Set loSource = Range("Source").ListObject Set loDest = Range("Dest").ListObject On Error Resume Next 'LoDest might have no data loDest.DataBodyRange.Delete On Error GoTo 0 For Each lrSource In loSource.ListRows Set lrDest = loDest.ListRows.Add 'lrSource.Range.Copy lrDest.Range '<= Don't do this. It will end in tears. lrDest.Range.Value2 = lrSource.Range.Value2 '<= Do this instead. It's faster anyhow. Next lrSource End Sub |
…which works fine, and is faster anyhow:
In case you’re wondering what happens if you bring the whole DataBodyRange through from the Source Table using that dangerous .Copy method i.e using code like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub CopyDataBodyRange() Dim loSource As ListObject Dim loDest As ListObject Set loSource = Range("Source").ListObject Set loDest = Range("Dest").ListObject On Error Resume Next 'LoDest might have no data loDest.DataBodyRange.Delete On Error GoTo 0 loSource.DataBodyRange.Copy loDest.Range.Offset(1).Resize(loSource.ListRows.Count) '<= Don't do this. It will end in tears. End Sub |
…then again the results depend on whether a cell is selected in the Source table:
…or not:
Again, avoid the inconsistency by setting the values of the second range directly to the values of the first:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub CopyDataBodyRange() Dim loSource As ListObject Dim loDest As ListObject Set loSource = Range("Source").ListObject Set loDest = Range("Dest").ListObject On Error Resume Next 'LoDest might have no data loDest.DataBodyRange.Delete On Error GoTo 0 'loSource.DataBodyRange.Copy loDest.Range.Offset(1).Resize(loSource.ListRows.Count) '<= Don't do this. It will end in tears. loDest.Range.Offset(1).Resize(loSource.ListRows.Count).Value2 = loSource.DataBodyRange.Value2 End Sub |
…which works fine, fast:
Here’s a sample file:LRCopy Test
@Jeff
Sub Test()
[Source].Copy [Dest]
End Sub
When the active cell is inside the List object the rules of copying from a filtered table apply (only visible cells get copied). When the active cell is outside it treats it as a normal range