VBA copy specified columns from one sheet to another using array

**Tunesmith** · 01-30-2019, 10:17 AM

Hi,

Looking for ideas, to copy specific named columns from a source sheet to another sheet created in the same workbook.
Due to the length of the column headers, and the sheer amounts of them in the source files, I'm hitting into the limits of what a single Array("Column_1", "Column_4","Column_6"...) can contain.

For the sake of this post lets say I need 150 columns scattered throughout of 300 in my source.
The exact amount of columns in the source sheet as well as their order can vary. Which is why I need somewhere to list the specific header rows that I need and then run through and copy only those to a Destination sheet.

Previously I've the below code to copy columns between/to sheets but it doesn't work with the amount of them in this case.

Please Login or Register  to view this content.

**TMS** · 01-30-2019, 10:53 AM

Why not just list the columns in a hidden worksheet and process that rather than an array?

**Fluff13** · 01-30-2019, 10:58 AM

Try it like

Please Login or Register  to view this content.

**Tunesmith** · 01-31-2019, 06:03 AM

Originally Posted by TMS

Why not just list the columns in a hidden worksheet and process that rather than an array?

Hadn't considered that, would that be listing the column in a table and using that as "the array" so to speak?
Say I have the list of columns in "Workbook_B" how would I write that? I'm interested in this as an alternative.

Originally Posted by Fluff13

Try it like

Please Login or Register  to view this content.

Thank you for this, it looks like a more efficient way of writing the array. The resulting array is a massive 5300 characters long but it works.

I'm interested in the alternative proposed by TMS as well, before marking as solved.

**Fluff13** · 01-31-2019, 10:02 AM

With a list of the columns you wanted in A2 downwards on a sheet called "List", you could use

Please Login or Register  to view this content.

**Fluff13** · 01-31-2019, 10:13 AM

You will also ned to tweak the loop through the array

**Tunesmith** · 01-31-2019, 11:55 AM

Originally Posted by Fluff13

You will also ned to tweak the loop through the array

Thank you!

I modified the code slightly to use a named table in my addin workbook that's loaded alongside the disposable files.
Code below for reference:

Please Login or Register  to view this content.

**Fluff13** · 01-31-2019, 11:59 AM

Glad you got it working & thanks for the feedback.

@TMS
Thanks for the Rep & happy to help.

VBA copy specified columns from one sheet to another using array

LinkBack

Thread Tools

Rate This Thread

Display

VBA copy specified columns from one sheet to another using array

Re: VBA copy specified columns from one sheet to another using array

Re: VBA copy specified columns from one sheet to another using array

Re: VBA copy specified columns from one sheet to another using array

Re: VBA copy specified columns from one sheet to another using array

Re: VBA copy specified columns from one sheet to another using array

Re: VBA copy specified columns from one sheet to another using array

Re: VBA copy specified columns from one sheet to another using array

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How can I array three columns from one sheet into another?

copy from sheet to array

How to copy an array to excel sheet?

[SOLVED] Copy columns from one sheet to next empty columns in another sheet

Macro to copy 3 columns of data from multiple sheets into 3 columns on 1 sheet

Copy value to Array variables use them in another sheet!

Copy from one sheet using an array

Bookmarks

Bookmarks

Posting Permissions