Set Range via Structured Reference get error on first vba run, but not second run

I have a macro from a large complex workbook, that allows a user to set a list of cell references, and have that data be exported to another workbook.

I recently transitioned to starting to using Structured Table references, which saves a lot of time when the layout of the sheets changes, as the code in VBA doesn't have to change to match.

The issue is that on the first time the macro runs after the workbook is opened, the line setting the Range variable fails if it is a structured reference.

Please Login or Register  to view this content.

Where expRng.Cells(i, "D").Value is the cell containing the range reference as a string, ie "CalcsTbl[[#Totals],[$ TOTsav/yr]]"

If I kill the macro and start again, it works perfectly.

Note that in the above code, intellisense sets range() to a lowercase word. If I copy the code to any other workbook, intellisense capitalises Range(), and I cannot recreate the bug.

Here is all the relevant subs, and a small example anonyminised workbook.

If anyone can suggest things that would cause this, or see any mistakes I'm making, it would be greatly appreciated

Please Login or Register  to view this content.

Didn't read whole, but my guess...

Please Login or Register  to view this content.

Originally Posted by jindon

Didn't read whole, but my guess...

Please Login or Register  to view this content.

Actually no. The table reference works like a named range, so it is used without a call to the parent worksheet

Originally Posted by jindon

Didn't read whole, but my guess...

Please Login or Register  to view this content.

Thanks jindon
This is frustrating.
Using the sheets reference actually works. I had been under the impression from early use of structured references that it didn't.
I generalised the code thus:

Please Login or Register  to view this content.

And I got rid of the lower case issue by adding

Please Login or Register  to view this content.

and then deleting it.

But none of this explains why

Please Login or Register  to view this content.

works on the second run of code, but not on the first run.

1) Please don't quote full post, unless it is really needed.

2) That's an Excel... Always better to add a parent object if it is known...

Originally Posted by jindon

1) Please don't quote full post, unless it is really needed.

Noted.

As for the parent issue, it looks like I'll need to expand a few of my data structures to keep the sheets. But one of the reasons I like the structured references was that I have been able to move items from sheet to sheet to suit users without breaking code. I no longer have to tell people to just start again if a warning message comes up, but it would still be helpful if I could understand this better.