Use Table Column Headers as .Cells(x,header)

[LlebKcir]

old thread: https://www.mrexcel.com/forum/excel...n-headers-cell-reference-search-criteria.html

Instead of using the column # in a cell/range reference I want to use the Table Column Header name. In workbook 1 the columns will continue to update over time, but the column names should remain the same (those that are currently there that is) and current columns might also be moved around over time.

Example of move:

Column B might be moved to Column Z at some future date, but the column header will remain Group.

To prevent having to manually update code down the line, I would like to use the column header in my code to search, grab values, perform calculations against, etc...

Data stored in workbook 1 is used as the data storage for many other workbooks. Data is only manually updated into workbook 1 today. That is the desire moving forward.

Copy/paste data or calculate/manipulate data from workbook 1 into other workbooks is performed often and repetitively. I would like to further automate one of those spreadsheets.

Currently in workbook 2, I copy/paste by hand 8 columns of data. In the paste that was all we required to use workbook 2. We are moving into the next phase of those projects and now we require roughly 50 columns worth of data. That is not something I am going to copy/paste by hand every time we need the data, nor is there a reason to store the data multiple times when we have workbook 1 as the repository of the data.

this will be a multi-part process. In part 1 I just need to learn howto use Table Column Headers in place of column # in my code. Part 2, I will worry about sorting and finding the correct row value.

instead of using code like this:

Dim wb1 as Workbook
Dim ws1 as Worksheet
Dim wb2 as Workbook
Dim ws2 as Worksheet

Set wb1 = \path\to\Workbooks.Master
Set ws1 = wb1.City_Name

Set wb2 = \path\to\Workbooks.Table
Set ws2 = wb2.Group_Number

wb2.ws2.Cells(2, i).value = wb1.ws1.Cells(10, Group).value

Not real code, just an idea of what I am thinking. .Cells(2, i) i would be used in either a for or a with loop. I am currently leaning to having the VB script reside inside workbook 2, not in workbook 1. I am open to change on that. Workbook 1 might be the better place to put the code in the long run. to be determined.

Partial example of data stored in Workbook 1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]City[/TD]
[TD]Group[/TD]
[TD]Position[/TD]
[TD]Data 1[/TD]
[TD]Data 2[/TD]
[TD]Data 3[/TD]
[TD]Data 4[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[TD]l[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]m[/TD]
[TD]n[/TD]
[TD]o[/TD]
[TD]p[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]q[/TD]
[TD]r[/TD]
[TD]s[/TD]
[TD]t[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]u[/TD]
[TD]v[/TD]
[TD]w[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]y[/TD]
[TD]z[/TD]
[TD]aa[/TD]
[TD]ab[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]ac[/TD]
[TD]ad[/TD]
[TD]ae[/TD]
[TD]af[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]


Potential use in workbook 2 on worksheet 'Group 1'

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Question[/TD]
[TD]Position 1[/TD]
[TD]Position 2[/TD]
[TD]Position 3[/TD]
[TD]Position 4[/TD]
[/TR]
[TR]
[TD]Data 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 2[/TD]
[TD]b[/TD]
[TD]f[/TD]
[TD]j[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Position[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 4[/TD]
[TD]d[/TD]
[TD]h[/TD]
[TD]l[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The blank fields would either be data not in Workbook 1, or just not shown in this example set.

 

[LlebKcir]

Is the question clear, or did I just confuse the board? Sorry if not clear enough, please tell me what info I can provide to help you help me.