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

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
206
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:

Rich (BB code):
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

CityGroupPositionData 1Data 2Data 3Data 4
New York11abcd
New York12efgh
New York13ijkl
New York21mnop
New York22qrst
New York23uvwx
New York24yzaaab
New York25acadaeaf
New York3110111213

<tbody>
</tbody>


Potential use in workbook 2 on worksheet 'Group 1'

QuestionPosition 1Position 2Position 3Position 4
Data 10
Data 2bfj
Data 5
Group111
Position123
Data 4dhl

<tbody>
</tbody>

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

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
206
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,771
Messages
5,542,433
Members
410,551
Latest member
Ali3ta1r
Top