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

LlebKcir

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

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,695
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top