VBA Table reference if working with a copy of the table

Please use Code Tags in your code excerpts to comply with Forum rules.

Please Login or Register  to view this content.

Perfect - that definitely did the trick!

Many thanks...

You're welcome. And thanks for adding the Code Tags

Ok - I came across another problem. In the above example, [[Measure1]:[Measure 3]] (effectively columns F - H of the table - these are the table column headers) can be renamed by the user. When they do however, the formula doesn't work any more.

Is there any way to reference named ranges if these aren't fixed names?

Please Login or Register  to view this content.

If they add or remove columns, you will have another different problem.

Well, great you mentioned it as it is an expandable table (rows and columns with initial 3 - 4 rows depending on table).

The main thing is that the values in columns A to E (A-D non-table info, E will become chart X Axis), have to remain untouched, the following columns (F onwards ) have to be changed according to the button click.

Would it work if the .Resize is removed from your code? (Don't have access to my workbook at present...)

Thanks again...

I'm sorry, it's impossible to anticipate and cater for all the changes that you anticipate that your users can/will make.

The initial problem was "how to cater for a changing Table name, resulting from copying the Table. Done ... original question asked and answered.

Your original code used Table Headers ... which can, apparently, be changed. Code for this rider has been provided ... use Offset and Resize relative to the Data area to refer to the columns whose headers may change. Question asked and answered.

Now you're saying that they can add and delete columns. Is there anything static to which you can pin this code?

If you're going to allow them to change the headers AND add, delete and shuffle them, I can't see how you're going to do it.

The only thought/suggestion is that you have a hidden row above your table and, in that, you have some static "Table headers". You could then use this to match your "expected" column headers with the columns, even though they may have moved. Assuming that columns may be deleted, you'd need to have error handling to cater for missing columns. You will have to handle the columns independently given that some of them may have gone missing.

Mmm, thought I might be pushing my luck. Simple project with more and more demands! Thanks for the help so far...