Referencing the a fix range of data

Hi guys,
I am working on an excel sheet involving macros. These macros insert  certain number of columns to the entire sheet. I want to cell reference the values received from macro to arrange them in an other sheet. Referencing them works for the first time. But when I change some values in the main sheet and re run the macro. my referenced cells moves from their place bcz macro is inserting columns. I want to fix my reference to particular range of data
Muhammad ZainAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
An example of the workbook containing the code would help.
Muhammad ZainAuthor Commented:
In the sheet, Macro sheet has a button which will enable my six macros to work in series. in Sheet 3, the values are referred to macro sheet. But if you remove the values of column e on wards. in Macro sheet and press button. My refereed cells will not give me proper value
Roy CoxGroup Finance ManagerCommented:
The example is VBA protected, but looking at your code you haven't made an comments about what the code should be doing and many of the variables like pyi do not seem to be declared.

I'll have to work through the code to discover what is supposed to be happening
Martin LissOlder than dirtCommented:
You code is protected so I can't look at it, but I believe what you need to do is to create Named Ranges from each of the columns and in your code refer to the Named Ranges. Excel will automatically change the ranges referred to if a column is deleted or added. I've attached a video of adding the Named Range for column F.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
If you have gotten the answer that you need then please close this question. If you don't know how to do that then please see this article.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.