-3

I have a report that provides a snapshot of information from the prior day, then I click a button (VBA code) upon completion and it takes data stored in certain cells on the report tab and moves it to another tab where its position is re-organized so I can track the data on a trend line.

On the Trend tab if I highlight column C, for example, and insert a new column it pushes all of the data to the right but the cell references in my VBA code don't change. So when I need to re-organize the trend tab to incorporate more/different data it's becoming more laborious to go back to the VBA code and update all of the cell references.

Is it possible to reference the cells in my VBA code, in a way that they will change in concert with the data in the trend tab if I move things around?

Mesut Akcan
  • 900
  • 7
  • 17
theteague
  • 383
  • 2
  • 9
  • 1
    Use Named Ranges instead of Cell references – Tom Nov 29 '18 at 14:38
  • Yes, using named ranges for this is the solution. Also you should create an easy to use name structure for these names. For example if I create a table, I use named ranges for every header. This way its very easy for me to add new columns to a table and I dont have to change my code. – Wallnussfolie Nov 29 '18 at 14:47
  • Nearly every time I ask a question it gets down-voted. What makes this a bad question? Thanks Tom and Wallnussfolie. I'll look into how to use Named Ranges. That's helpful. – theteague Nov 29 '18 at 16:35
  • 1
    @bteague for the most part, general questions that are not specific to the code you've already generated will be downvoted and flagged for closing, as they tend to be subjective, rather than finite/absolute. the stackexchange collective has lots of avenues, and in some cases, those flags will state that a post should be moved to another site, e.g. code review for an optimizatino question posted to stackoverflow. that is my take away after my time spent here, at least. – Cyril Nov 29 '18 at 16:42

1 Answers1

0

If anyone else has this question this is great info.

https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/refer-to-named-ranges

theteague
  • 383
  • 2
  • 9