Q: VBA: copying excel columns: best practice for storing cell references? I have a big file with data, updated weekly, from which a VBA script copies a lot of columns of various lengths and starting points, and then pastes these columns one by one into another file. 
My question is how to best store the cell references that the script needs to be able to copy the correct columns? Currently there is a bunch of arrays storing the starting row number, starting column number, sheet number etc which are all indexed the same, and a loop function which does the actual copy paste work. 
This (exceptionally bad?) solution would obviously be an absolute nightmare if the source file would change slightly at some point. So how should one do it better? 

A: Excel is using enumerations for this purpose. Enums are the most efficient way to assign names to constants.
[Private] Enum WeeklyReport
    FirstRow = 3
    StartColumn = 1         ' Columns:
    Text
    Values
    Totals = 17
    Remarks
End Enum

The above declaration specifies the enum WeeklyReport with 5 values, one row and 4 columns. Text has the value of 2 because if the value is omitted the previous is incremented by 1. Therefore, Remarks = 18.
You can call up the values by their full name, like, WeeklyReport.Remarks or by their short name, like Remarks. That's why Excel gives unique names to its enumerations, like xlUp, which you might be using all the time without even knowing the enumeration's name. The names given above are possible but not very good.
Enumerations are a data type of their own which is interchangeable with Long. Declared before any code, at the top of a code sheet, they are available throughout that sheet if Private, otherwise throughout the project.
