VBA to change reference cells of a name range with different cells

[IronM]

Hi all,
I am trying to find a solution to a code (attached to a button) that will change the referenced cells of a Named Range to different cells.
So, I have in the sheet System the cells C6:41 (part of a table) named "plan". I would like to press a button and have the name "plan" refer to cells D6:41 in the same sheet.

Can I get some help pls?

Thank you in advance

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub IronM()
   ThisWorkbook.Names("Plan").RefersTo = Range("Plan").Offset(, 1)
End Sub

 

[IronM]

Thank you, for the code and the welcoming.

It works as intended but only partially suits my objective. Now, every time I run it, it will go one row further and I need it to revert back to the initial reference. (so just between column C and D - like a toggle).

On a bigger picture: I have another code that runs depending on column C (planned dates). Now, I want to run the same code but for column D, when I press a button/toggle/switch...to run the code on the forecast dates. And so I can change my chart depending on which set of data I need.

 

[Fluff]

In that case

Sub IronM()
   If Range("Plan").Column = 3 Then
      ThisWorkbook.Names("Plan").RefersTo = Range("Plan").Offset(, 1)
   Else
      ThisWorkbook.Names("Plan").RefersTo = Range("Plan").Offset(, -1)
   End If
End Sub

 

[IronM]

Great. This pretty much solves it, with the mention (for those who will search in the future ) that "3" is the number of the column where the initial namerange is (A=1, B=2...).

One more thing and we can close this: is there a way to add this code to a 2way button so when I click once it will show Forecast and click second will say Planned and cycle between these 2? Doesn't necessary needs to be a visual change of the button but at least for the text to appear so I know on which column the name range moved to.

 

[Fluff]

As that is a different question, it needs a new thread. Thanks

 

[IronM]

Thank you so much for your help!!

 

[Fluff]

You're welcome & thanks for the feedback.