Make VBR independent of column order in data table, output results in non-adjacent columns

Hi!

I have a VBA code that I need to modify and would appreciate some help on that.

The code fetches some data from a worksheet named “dws” and then reorganizes those data in a worksheet named “ows”.

The first five columns in the worksheet “dws” are used to get the data, along with some criteria in a worksheet named “mws”.

The name of the first five columns in “dws” are as follows:

Batch, Batch2, Material, Filter Code, Deleted

The name of the three first columns in "mws" are as follows:

solDict, halbDict, packDict

The code addresses the column “Deleted” by name but addresses the other columns by use of the Offset function. However, the order and placement of these columns in “dws” may change from time to time and this would cause the code to stop working because of the way these columns are addressed.

The data worksheet “dws” may reside within the same workbook as the output worksheet “ows” but will most often reside in a separate workbook at a different address.

The data that is fetched from “dws” will eventually be pasted in the columns N, O and P in the worksheet “ows”, starting in cell N9.

I would appreciate help on the following three items:

1. Make the code independent of the placement of the columns in “dws” and "mws" by using each individual column name, like has been done for the column “Deleted”.

2. Make the code look for filepath and filename in cell A1 of the output worksheet “ows”, so that this can be changed easily.

3. Make the code output the data in the non-adjacent columns N, R and V instead of the adjacent columns N, O and P, but still starting at the ninth row as it already does.

Here is the code:

Please Login or Register  to view this content.

I have attached a workbook that includes the code and some data so you can see how it presently works.

The file path needs to be written correctly in the code before it can be executed:

Please Login or Register  to view this content.

Best regards,
Marbleking