Excel VBA - Set ListColumn on one worksheet equal to ListColumn on different worksheet

• Oct 15, 2020

• #2

S

SkipVought

Programmer

Dec 4, 2001

47,492

US

Hi,

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

Upvote 0 Downvote

• Oct 16, 2020

• Thread starter
• #3

jmarkus

Technical User

Oct 15, 2002

124

CA

Because of the way I am trying to work with the data from old to new, it is possible that the two tables won't be identical, but they will have identical columns (e.g. the wbA workbook table might have additional columns not present in the src workbook table). That is why I am trying to set column by column and not the entire table.

Does that make sense?

Thanks,
Jeff

 

Upvote 0 Downvote

• Oct 16, 2020

• #4

S

SkipVought

Programmer

Dec 4, 2001

47,492

US

I've been basing my reply on "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)" because you haven't really disclosed your complete objective.

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

Upvote 0 Downvote

• Oct 16, 2020

• Thread starter
• #5

jmarkus

Technical User

Oct 15, 2002

124

CA

Skip,

I appreciate your straightforward-ness. My original enquiry was:

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.

Click to expand...

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

 

Upvote 0 Downvote

• Oct 16, 2020

• #6

S

SkipVought

Programmer

Dec 4, 2001

47,492

US

If your table has columns named A to Z and you only want A, M and Q, that can be done with the suggestion I made by coding...
[tt]
SELECT A, M, Q
From...
[/tt]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

Upvote 0 Downvote

• Oct 19, 2020

• Thread starter
• #7

jmarkus

Technical User

Oct 15, 2002

124

CA

I took the 'easy' way out. Thought I could reduce to one line of code and not resort to copy, but moving on now:

Code:

src.Sheets(Sht).ListObjects(tbl).ListColumns(col).DataBodyRange.Copy
wba.Sheets(Sht).ListObjects(tbl).ListColumns(col).DataBodyRange.PasteSpecial (xlPasteValues)

Jeff

 

Upvote 0 Downvote

• Oct 19, 2020

• #8

S

SkipVought

Programmer

Dec 4, 2001

47,492

US

That is definitely NOT the "'easy' way out."

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

Upvote 0 Downvote