Copy Excel 2010 table(listobject) INCLUDING hidden/filtered rows.

[jeffreyweir]

Howdy all. i'm trying to copy a whole bunch of tables with identical layount 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.

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.

Anyone got any ideas?

Regards

Jeff

 

[jeffreyweir]

Hmmm... It seems you get inconsistent cut/paste behaviour 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 only 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.

Note I also posted on this at http://yoursumbuddy.com/copy-table-data-while-not-breaking-references/