×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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:

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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

(OP)
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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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

(OP)
Skip,

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

Quote:

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.

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

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...

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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

(OP)
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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close