Copying source columns to different columns in different workbooks with dynamic variables

[youngtusk87]

Hey folks! Just had some logic questions regarding my macro in progress. Right now I am trying to copy multiple columns from my source workbook(called "Master_Template") and paste them into several other workbooks. The problem is that each of the destination workbooks are organized differently and cannot be changed. As you can see below, my current code works to copy one column to the others. But if I stay to this course I have to define each source column manually(there are 10 columns to be copied) and define each destination per column.

When I started thinking of a solution involving different variables for each workbook or column I thought myself into a brain fart and now I got nothing. I know there is a much more efficient way of does this. Any helpers?

Please Login or Register  to view this content.

(Note: This is just a snippet of code, so if it doesn't look like it will work it may be because it is apart of a larger macro Thanks in advance!)

[rylo]

Hi

Various posibilities.

1) Create a few arrays that have the relevant parts of it all, in the same order, so the same index can be applied to all the arrays.
eg
wbarr(1) = "Sears_Addon_Template"
wbarr(2) = Newegg_Addon_Template"
sharr(1) = "Data Format"
sharr(2) = "Datafeed"
outrng(1) = "G2:G200"
outrng(2) = "G3:G201"

Then once you make the first copy, you cycle through the various arrays to build the output string.

2) Build a class module and house a user defined type with the relevant details in an array
3) Build a user defined type and put them into an array of the same type.
4) Have all the details noted in a sheet in the file that contains the macro, then cycle through them all

Whatever you do, you will have to do something to contain all the output variables. However, you should be able to build something that will be able to be looped, and therefore easily expanded.

HTH

rylo