Excel VBA - Set ListColumn on one worksheet equal to ListColumn on different worksheet
Excel VBA - Set ListColumn on one worksheet equal to ListColumn on different worksheet
(OP)
I have a blank structured table in one workbook and I wish to populate it column by column from another table in another workbook with the same sheet, table, column names.
I tried this:
But that doesn't seem to work, I'm guess because the DataBodyRange sizes are different (blank table has one row, source table has X number of rows).
Can someone suggest the proper way to populate without using select or copy/paste?
Thanks,
Jeff
I tried this:
CODE
wbA.Sheets(Sht).ListObjects(tbl).ListColumns(col).DataBodyRange.Value = src.Sheets(Sht).ListObjects(tbl).ListColumns(col).DataBodyRange
But that doesn't seem to work, I'm guess because the DataBodyRange sizes are different (blank table has one row, source table has X number of rows).
Can someone suggest the proper way to populate without using select or copy/paste?
Thanks,
Jeff
RE: Excel VBA - Set ListColumn on one worksheet equal to ListColumn on different worksheet
You can Query the Structured Table so that the target table is identical to the source table at the time the query is executed AND Refresh the target QueryTable to display new source table results at any time.
BTW, the new target table behind the new QueryTable, will be a Structured Table, just not YOUR Structured Table.
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Excel VBA - Set ListColumn on one worksheet equal to ListColumn on different worksheet
Does that make sense?
Thanks,
Jeff
RE: Excel VBA - Set ListColumn on one worksheet equal to ListColumn on different worksheet
I addressed your original question.
What you are now posing adds a new level of complexity!
What happens in vagueness stays in vagueness.
Are we finished yet?
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Excel VBA - Set ListColumn on one worksheet equal to ListColumn on different worksheet
I appreciate your straightforward-ness. My original enquiry was:
So ultimately that is what I would like to do. I thought that I could populate one ListColumn.DataBody range from another, but I seem to get mixed up with hole DataBodyRanges, Ranges, Range.Values and all the other things that sometimes seem similar enough to do what I want.
Any additional insight is appreciated.
Thanks again,
Jeff
RE: Excel VBA - Set ListColumn on one worksheet equal to ListColumn on different worksheet
SELECT A, M, Q
From...
But you threw spitball. But maybe I assumed too much. If all you want is to replicate selected columns, then there's no spitball and you have your solution.
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Excel VBA - Set ListColumn on one worksheet equal to ListColumn on different worksheet
CODE
Jeff
RE: Excel VBA - Set ListColumn on one worksheet equal to ListColumn on different worksheet
1. You must have VBA code.
2. You are getting all columns instead on the exact one(s) you need.
My solution, uses native Excel built-in features, returns ONLY the columns you specify and executes on a Refresh command to return new data at any time.
You just revealed only 2 lines of code. Maybe you're getting only the columns you need. But if the source data table structure ever changes, affecting the column position of your columns of interest, you'll need to modify your VBA code.
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein