Referencing the a fix range of data

Muhammad Zain
Muhammad Zain used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
An example of the workbook containing the code would help.

Author

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
mysheet.VBA.xlsm
Roy CoxGroup Finance Manager

Commented:
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
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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.
2018-04-22_10-30-35.mp4
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial